Union query returns date as Text which doesn't sort correctly

[magicdds]

I created a union query which returns dates (into a field called STATEDATE)
like
5/19/07
6/23/08
4/3/07
etc.

But the dates returned are in Text format (When the query results display,
the dates are left justified in the column instead of right justified, like
dates normally are displayed)

So when I created another query to include to include STATEDATE, and I sort
on STATEDATE Ascending, the dates don't sort correctly.

How can I get the results converted over to a date that can be sorted
correctly?

Thanks for any help!

 

[raskew via AccessMonster.com]

The DateValue() function will convert text dates. Example:

x = "6/15/08"
y = datevalue(x)
? y
6/15/2008
to show that y is in fact a date:
? cdbl(y)
39614

It would appear that field STATEDATE is a text field, not a Date/Time field.
That needs to be corrected.

HTH - Bob

 

[magicdds]

Thanks for your help

The DateValue() function will convert text dates. Example:

x = "6/15/08"
y = datevalue(x)
? y
6/15/2008
to show that y is in fact a date:
? cdbl(y)
39614

It would appear that field STATEDATE is a text field, not a Date/Time field.
That needs to be corrected.

HTH - Bob