Compare multiple fields in record

[Can]

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?

 

[Duane Hookom]

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.