Hi, I have a Query, which is supposed to bring back the max date (The
greatest Date) of all records from a table.

The table is an Order Details Table relating on the many side to a master
Order Header table.
It is used to get the ETA's for the parts, get the last date relating to
that particular order and return that value to my Orders Header Table in the
Overall ETA. By this VBA: (ValeID is the PK, DepotETA is the ETA of each part
on the Order Details and the OverallETACalculation is the Query))

Private Sub OverallETA_DblClick(Cancel As Integer)

Me!OverallETA = DLookup("[MaxOfDepotETA]", "OverallETACalculation",
"[ValeID] = Forms!Orders!ValeID")

End Sub

The problem I have is this. My DepotETA cannot be formatted as a Date/Time
Field because if there is no ETA I have to put the Text in "Trying To Source"
Therefore has to be formatted as a text field. This results that the Max
lookup looks up the maximum of the first to digits of the date before the "\"
identifier.

Therefore looks at the highest value compared to days.
IE -

dd /mm/yy

07/01/04 is returned instead of 05/01.04 because 07 is higher in value then
05 (I'm using English dates by the way, because I'm in the UK)

However it does return the text to my Order Header "Trying To Source"
instead of a date.

Can anyone suggest a way around this problem, where my query could look at
the whole date rather than the days?


Thanks very much in advance

Andi Lee Davis.