The only reliable solution will be to change the text field into a date/time
field. Leave it blank if you are still "trying to source". You can actually
put the text "Trying to source" onto your report in place of Null if you
wish.

If you cannot do that, you will have to store the text value of the date
like the Koreans do as yyyymmdd, so that the most significant parts are
first. Otherwise it will not sort correctly.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Andi Lee Davis" wrote in message
...
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.