Problem with dlookup field sorting correctly

[Guest]

I have a field in a query using dlookup to find a minimum date in a table but
it does not sort by date. I have tried formating it but it still sorts by
text.

delivery: Dmin("MyDate","Mytable","MyID = ID")

I do receive the correct date with the dmin but I cannot sort the table by
the delivery field by date. It sorts by first numeric number

ex:
1/7/2007
11/3/2006
2/5/2006
2/22/2007



I have tried
delivery: Dmin format(("MyDate","Mytable","MyID = ID"),"mm/dd/yy") Did not
work
delivery: Dmin format(("MyDate","Mytable","MyID = ID"),"Short Date") Did
not work

For some reason it's treating it like test

The Mydate field it is looking up is formated as date also.

Can some one please help

 

[Duane Hookom]

DMin() returns a variant text type field. Try wrap your DMin() in
CDate(DMin(....) ).

 

[Guest]

I learn something new everyday.

Thanks Duane! That worked

 

[Duane Hookom]

Glad it worked. I also learn something new everyday.