calculated field maximum date

[philfer]

Hi,

I have a database table which holds an import file.

There are three date fields (as well as loads of other fields):-

Maturity date Final Maturity Date Limit Exp Date


I want to run a query that pulls a few fields and then a field called "max Date" which has the latest of all three dates.

In excel you can use the "max" funtion with dates to get the last date.

I tried this in the expression builder in query designer but with no success.

Can anyone think of how I can do this?

(I dont like using custom VBA functions in query design as I find it slow!)

Cheers
Phil

[CreganTur]

The only way I can think of to do this is using nested IIF statements to compare the three data fields. Something like:
[VBA]IIF(Date1 > Date2 AND Date1 > Date3,Date1,IIF(Date2>Date1 AND Date2>Date3,Date2,IIF(Date3>Date1 AND Date3>Date2,Date3,null)))[/VBA]

HTH

[OBP]

Taking Randy's solution you may well be able to then Max that, I take it you are aware of the Max function in access queries under "Totals"?

[philfer]

Hi Guys,

Thanks for that.

Would I need to use the Max function in a totals query after using Randy's answer. Randy's will seem to give me the latest date per record which is what I was after.

The Max selection in a totals query will only give me the Max date in a grouping, no?

Cheers guys

[OBP]

As you say it will only give you the Max date if you have a group of similar records, it will then find the last date.
But if Randy's answer does what you want ignore my remark.