Compare multiple fields in record

I have 5 different date fields in a record, I need to
compare them to see which date is the most current. Some
fields may be null.

I have a module, then I reference the function from the
module through a query. I get an error "compile error. in
query expression" when I try to run it.

I was going to scrap the module and try to get the result
a different way but my brain is fried and I can not think
of another way to do this.

Any ideas?

It seems that your data might not be normalized. One method to grab the most
recent date is to create a union query from your table
Select [DateA], "A" as TheDate, Field1, Field2
From tblYourTable
UNION
Select [DateB], "B", Field1, Field2
From tblYourTable
UNION
Select [DateC], "C", Field1, Field2
From tblYourTable
UNION
Select [DateD], "D", Field1, Field2
From tblYourTable
UNION
Select [DateE], "E", Field1, Field2
From tblYourTable;
You can then Select the Max([TheDate]) from the Union query.


--
Duane Hookom
MS Access MVP


"Can" wrote in message
...

I have 5 different date fields in a record, I need to
compare them to see which date is the most current. Some
fields may be null.

I have a module, then I reference the function from the
module through a query. I get an error "compile error. in
query expression" when I try to run it.

I was going to scrap the module and try to get the result
a different way but my brain is fried and I can not think
of another way to do this.

Any ideas?