How to query all fields of a record to query the highest value?help!

[Guest_imported]

Dear Friends,
I have 8 date fields for a record and would like to 'pick up' the latest date of all for that particular record only and to be listed in a ninth field for that record.How to do this?Any help is greatly appreciated.
Row: Certification
8 date fields for: Renewal dates once in a year
(not all the fields will be filled in a same manner for all records.Meaning, some may have only three dates filled in, some may have six dates filled in at any point of time, etc.,)
Thanks in advance.
Antony.

 

[DougP]

Well look at the MAX function

 

[AussieTony]

Thanks Doug,

The Max function only pulls the max value of one particular field. I am looking at the max value of all fields for one particular record (max value horizontally). Any help!

Regards,
Tony.

 

[LarryDeLaruelle]

You might try using an expression in the query that compares the value of the second date field to the first, then the third to the second, etc.

IIF(Date2 > Date1, Date2, IIF(date3 > date2, date3, IIF( etc, etc.

If you haven't used the IIF statement in queries you should look it up in help. But generally, it is easy to use; format is:
"IIF(Test, Result if True, Result if False)"

You can nest the IIFs where the next IIF is placed in the third position (Result if False). And you'll need to end the expression with the same number of close parands as you have open parands.

One note of caution. I think the expression length is limited to 255 characters but you should be alright here if your field names are not too long.

Or, if you have used VBA, you could create a function that does the same thing using IF and ELSEIF.

Either way, you should end up with is the last certification date in the column where you put the expression or function call.

Hope this helps.

 

[AussieTony]

Thanks LarryDeLaruelle. I am going to try this. Thanks again for your help!

Antony.