IIF() evaluates incorrectly?

[1eyedjack]

I have been staring at this for hours. If anyone can see what I have done wrong I would be so grateful.

In query Q_CTPayDeadline_1
Record ID_Tasks = 194
Field [Stage1B] SHOULD (I believe) evaluate to date 2010-01-01, not 2004-03-31, the former being greater than the latter.

The criteria for field Stage1B is
Stage1B: IIF([NzCease]>[PriorEnd],[NzCease],[PriorEnd])
The values of NzCease and PriorEnd are displayed to the right of Stage1B, for quick reference.
As you can see, for recird ID_Tasks = 194, the values shown are
NzCease = 2010-01-01
PriorEnd = 2004-03-31
BUT
Stage1B = 2004-03-31
ie it treats the IIF() function as returning false, when it should be true.

 

[ideefixe]

Since you have the dates in SQL Server format, you might have problems with the "greater than" comparison evaluating the date correctly. You are better off using a date-specific function such as DATEDIFF in your Iif function call, or better still, design a specific function to compare two dates and return the one which is greater than the other.

Hope this helps

 

[1eyedjack]

Many thanks.

And my apologies for posting in the "Reports" subforum when I meant to post in the "Queries" subforum. Just noticed.

PS. It also seems to be recommended that if you use the Nz() function on a date field then better to use
Datevalue(Nz()) else you risk getting a date that is treated as a text field. Uff! Helpful behaviour? Not!