MAX of Date Error

[Alfie2008]

Hi,
I have written an iif statement in query design grid as follows;

IIf([tblCustStatusLookup]![stsStatusID]=2,[stsDate],"Cancelled")

I have specified MAX in the design grid Total field

It returns a date but not the correct MAX eg. out of the following dates;

06/11/2010
17/03/2010

I would expect 06/11/2010 but it returns 17/03/2010

It seems to be taking the MAX of the number eg. 17

How do I amend to return the MAX date ?

thanks

Ian

[hansup]

In your table design, what is the data type for stsDate?

[Alfie2008]

The field is date/time formatted as dd/mm/yyyy

[hansup]

Good! Here's what I think is happening: Your IIf expression returns 2 different data types: Date/Time when True; String when False. So, when you apply the Max function to your IIf expression, the database engine transforms stsDate to its string equivalent.

See whether changing the IIf expression to this gets you closer to what you want.

IIf([tblCustStatusLookup]![stsStatusID]=2,Format([stsDate], "yyyy/mm/dd"),"Cancelled")

If you don't find joy from that suggestion, switch your query from Design View to SQL View and copy the SQL into your message. That may give us a better chance to figure out what's going on.

[Alfie2008]

that worked great. I did get it to work using LAST but this could throw up other issues so thanks very much for your help!

[geekgirlau]

Dates in all Microsoft products are very US-centric (no matter what your regional settings are), so any time you are evaluating dates, it's a good idea to be very specific about the date format in your expression!