InstructionWhich7142
Registered User.
- Local time
- Today, 04:28
- Joined
- Feb 24, 2010
- Messages
- 199
I've got a query that's evolved over the years, the field in question has an IIF statement resulting in in one of two dates (A & B) and the criteria I now need to apply to it is an IIF that also results in one of two dates (D & E), C and F are a boolean field I need to check as that affects which date is used
I'd like to convert this to 2 fields with simple criteria not containing IIFs
The reason for this is Access' Query Optimiser automatically refactors the IIfs into a mess of statements that don't give correct results
Using two queries gives the correct result so I know the logic is "correct" but I'd like to understand the process of breaking this logic down for future usage as well
I understand that I can convert
Into multiple lines like:
(A>B) and F
C and (D>)
C and F
That bit is like mathematical algebra and you can "show your working" however I'm not sure how to handle the "Result" part of the IF in the same way, I'm not sure how to "expand the brackets" for that bit
I sort of know the full statement for the first one would be
But I'm struggling to explain why
Code:
Query Expression: iif(A>B or C, A, B)
Code:
Criteria Expression: <>iif(D>E or F, D, E)
I'd like to convert this to 2 fields with simple criteria not containing IIFs
The reason for this is Access' Query Optimiser automatically refactors the IIfs into a mess of statements that don't give correct results
Using two queries gives the correct result so I know the logic is "correct" but I'd like to understand the process of breaking this logic down for future usage as well
I understand that I can convert
Code:
(A>B or C) <> (D>E or F)
Into multiple lines like:
(A>B) and F
C and (D>)
C and F
That bit is like mathematical algebra and you can "show your working" however I'm not sure how to handle the "Result" part of the IF in the same way, I'm not sure how to "expand the brackets" for that bit
I sort of know the full statement for the first one would be
Code:
A>B and F and A <> D
But I'm struggling to explain why