Refactoring a query criteria with an IIF against a field also containing an IIF (1 Viewer)

[InstructionWhich7142]

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

Query Expression: iif(A>B or C, A, B)

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

(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

A>B and F and A <> D

But I'm struggling to explain why

 

[theDBguy]

Hi. Just a guess, but maybe something like:

...WHERE ((A>B OR C) AND (D>E OR F) AND A<>D) OR ((A<B OR NOT C) AND (D<E OR NOT F) AND B<>E)

 

[InstructionWhich7142]

Yea something like that, and the kinda alternative half of each one but yea, that's the kind of thing,

So I guess my question is for working stuff out like this in future is there some way to write an IIF in a truth table or how do I do algebra with something like this to explain

 

[theDBguy]

Yea something like that, and the kinda alternative half of each one but yea, that's the kind of thing,

So I guess my question is for working stuff out like this in future is there some way to write an IIF in a truth table or how do I do algebra with something like this to explain

No clue, really. Sorry...

 

[vba_php]

is there some way to write an IIF in a truth table or how do I do algebra with something like this to explain

it may be easier to use data in a table for the purposes of doing comparisons but i would guess the query might get long or you would end up using too many stacked queries? might be best just to stick with IIF(). I've never used a truth table so I wouldn't know either:


https://en.wikipedia.org/wiki/Truth_table