Query IIF Function - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Query IIF Function

Amanda McDonald

3 years ago

I have this IIF function in my query.

TechPay: IIf([TechPayOverride]<>0,[TechPayOverride],[TechCalc])

It works as expected except when the value entered into the TechPayOverride field is a negative number. If the value entered into the TechPayOverride field is negative the result is always 0. How can I get the negative result to populate in the TechPay field?

All the fields are formatted as currency.

	John Davy @Reply 3 years ago
What is the default value or value of TechCalc?

	Amanda McDonald OP @Reply 3 years ago
There is no default value set for any of the 3 fields.

Amanda McDonald OP @Reply

3 years ago

TechCalc value is also an IIF function.

TechCalc: IIf(([TechPayCategoryID]=10) And ([WorkOrder].[TechID]=[Tech].[TechID]),[Profit]*[Tech].[REPAIRS],IIf(([TechPayCategoryID]=3) And ([WorkOrder].[TechID]=[Tech].[TechID]),[Profit]*[Tech].[DIAG],IIf(([TechPayCategoryID]=5) And ([WorkOrder].[TechID]=[Tech].[TechID]),[Profit]*[Tech].[DRAIN+],IIf(([TechPayCategoryID]=6) And ([WorkOrder].[TechID]=[Tech].[TechID]),[Tech].[FC],0))))

	John Davy @Reply 3 years ago
I believe you are making TechCalc 0 Examine your IIF statement carefully.

Amanda McDonald OP @Reply

3 years ago

So I was wrong when I said that the result was always zero it's not.

The IIF statement that isn't working with the negative amount is the TechPay IIF statement.
TechPay: IIf([TechPayOverride]<>0,[TechPayOverride],[TechCalc])

It works as long as the TechPayOverride is zero or a positive amount.
But it doesn't work if it's a negative amount. It pulls the TechCalc instead of the TechOverride.
I'll attach screenshots of the form.

	Amanda McDonald OP @Reply 3 years ago

	Amanda McDonald OP @Reply 3 years ago

	Amanda McDonald OP @Reply 3 years ago

	Kevin Robertson @Reply 3 years ago
Is this what you are looking for? `TechPay: IIf([TechPayOverride]=0,[TechCalc],[TechPayOverride])`

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View