Result of Expression not sorting as Date

[dar149]

I have a query that finds the minimum date value from three date fields. When I sort the result it sorts as text rather than by the actual date. I have used the following expression:
Minimum Date: Minimum([GLExp],[UmbExp],[WCExp])

I tried formatting the field as a date, but if I sort or use Minimum Date in another query, it sees that field as text.

Any suggestions?

 

[PHV]

Why not posting the code of your Minimum function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dar149]

I created the expression in a query field.

Minimum Date: Minimum([GLExp],[UmbExp],[WCExp])
is one of the fields.

 

[dar149]

This is the code of the query:
SELECT [Contractor Table].Contractor, [Contractor Table].Administrator, Minimum([GLExp],[UmbExp],[WCExp]) AS [Minimum Date]
FROM [Contractor Table]
WHERE (((Minimum([GLExp],[UmbExp],[WCExp]))<>0) AND (([Contractor Table].Active)=Yes))
ORDER BY Minimum([GLExp],[UmbExp],[WCExp]);

 

[PHV]

Minimum, AFAIK, is not a standard JetSQL function, therefore you must have your own version.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dar149]

What would you use in place of minimum?

 

[PHV]

OK, I rephrase my question:
where is your Minimum function coming from ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dar149]

I have no idea, I just typed it in and it gave me the lowest date from whichever field had the lowest date for each record.

Is there a better way to do this? I have three fields in each record that contain dates. I want the least recent date of the three fields to display for each record.

 

[PHV]

OK, as nobody knows nothing, you may try to replace this:
ORDER BY Minimum([GLExp],[UmbExp],[WCExp]);
with this:
ORDER BY CDate(Minimum([GLExp],[UmbExp],[WCExp]));

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886