Where then Else? formula

[jarett]

I have a "WHERE" statement in my last query

WHERE (((CDate([DateCreated] & " " & [Order Time]))>DMax("dtmQueryLastRun","tblQueryLastRun")));

I need to add another parameter if the (CDate([DateCreated] & " " & [Order Time]) is < DMAX.... then WHERE (((CDate([NEWDATEFIELD])>DMax("dtmQueryLastRun","tblQueryLastRun")))

Can I add this to the same SQL or do I need to add another query?

 

[Micron]

I've never seen the 'if true' and 'if false' parts of IIF contain operators such as >. The basics of IIF is 'do this test'. If True, return this, else return that. You're tests (>) in the 'if true' and 'if false' parts will probably return True or False as criteria, which would never work. Also, why is your data split in to date and time? Now you have to string it together then convert to date/time when you could just store Now() in the table. If you must use these comparison operators in the True False portions, you'll probably have to nest IIFs, in which case this will get very ugly. Maybe a db copy would help those trying to help. Too bad it's not allowed here like it is just about everywhere else.

 

[jarett]

This worked

WHERE IIF(CDate([DateCreated] & " " & [Order Time])>DMax("dtmQueryLastRun","tblQueryLastRun"), CDate([DateCreated] & " " & [Order Time])>DMax("dtmQueryLastRun","tblQueryLastRun") , CDate([DateUpdated] & " "  &[Update Order Time])>DMax("dtmQueryLastRun","tblQueryLastRun"))

NEWDATEFIELD=[DateUpdated] & " " &[Update Order Time], will just have to watch to see if there are any inconsistencies.