Query Calcs using dates

[Saudi Mike]

this may seem silly, but I have a query in which I want to create a field called "qryLastDate" that will contain the later of two dates, Rev1 and Rev2. In the datasheet, sometimes it works and sometimes it's backwards. What am I missing? Thanks!

qryLastDate: IIf([Rev1]>[Rev2],[Rev1],[Rev2])

A sample of what it returns is attached.

 

[sneuberg]

I recreate what you have and cut and pasted the expression qryLastDate: IIf([Rev1]>[Rev2],[Rev1],[Rev2]) from your post into my query and it works correctly.

I've attached my version. Maybe you can see what's different. Since we know the expression is right, maybe it's something to do with the data types of Rev1 and Rev2 in your version.

 

[Saudi Mike]

I wouldn't this it's the data type. They're just dates. How odd. But thanks for looking! I'll go back and double check the types but i'm sure they're fine. What's so odd is that it generally works, but only occasionally seems to get backwards.

 

[Saudi Mike]

Getting into more detail, the Rev1 and Rev2 dates are also calculated in the query, but they display the correct dates. I don't think it could be because of multiple calculations, could it?

 

[namliam]

date fields are right oriented, your fields in your pic are left oriented.

The values may be dates but are being treated as text values, most likely a result of your calculation?

 

[Saudi Mike]

Thanks. I appreciate the thought, but I don't see any reason why these should be left justified. And again, it works sometimes, but on occasion not.

 

[Saudi Mike]

Side note: The view I attached was from the query, not a table. maybe that's why they're left justified?

 

[namliam]

The reason it works sometimes is because the dates have the same format... i.e M/DD/YYYY, all the broken records will have different formats and most likely have days in the range of 3-9... M/D/YYYY

7/9/1999 > 7/21/2015, simply because 9 is greater than 2

Show me any broken that has the same format ... you wont find any...

This is because of a text format instead of a date format on the field, not sure how or where the conversion happens but it does happen.
Try using

IIf(cdate([Rev1])>cdate([Rev2]),[Rev1],[Rev2])

I think you will find it helps to fix your broken lines

 

[Saudi Mike]

Ah.. Duh!! Silly me. Sometimes I miss the most obvious! Forgot all about cDate. You were correct, sir and I appreciate getting the cobwebs shook to get some of the dust off!

Thanks a mil!

 

[namliam]

Using CDate though is NOT the ultimate solution! Cdate is patchwork to cover up an earlier problem.

The ultimate fix is to get the dates here in tact as proper dates instead of as a text value, thus as I understand fixing your calculations before ... or even fixing your fields in your database.