Latest Date Between Separate Columns in the Same Table

[EHittner]

I'm trying to get a result -- in a new column -- that displays the latest date between three date fields in a single table, using the query builder. In the query builder, I entered the following...

LatestDate: MAX([Bios Received], [ASC_Appt_Date1], [ASC_Appt_Date2])

Before being able to run it, I'm getting the following error: "The expression you entered has a function containing the wrong number of arguments."

I'm thinking this is probably a very simple oversight -- but I'm not seeing it. Any help is greatly appreciated.

[Gicu]

You cannot use MAX like that (it is meant to return the max value within one field). I suggest you add these two functions in a standard module:

Public Function Min2(a As Variant, b As Variant) As Variant
'
' Returns the lesser of 2 values
'
  Min2 = IIf(a < b, a, b)
End Function
Public Function Max2(a As Variant, b As Variant) As Variant
'
' Returns the greater of 2 values
'
  Max2 = IIf(a > b, a, b)
End Function

Once you do that your expression would be:
LatestDate: Max2([Bios Received], Max2([ASC_Appt_Date1], [ASC_Appt_Date2]))

If any of the three dates are missing you need to use the Nz() function to return an alternate value (such as today's date or whatever your business needs require).
Cheers,