date sort in Access 97

I'm trying to sort results of a query of a number of fields from two
tables by a date field in one of them. However, it keeps sorting the
date based only on the first number ("day") of the date (ex. 1/1/2004,
2/1/2003, 3/1/2004...) and not the month or year. Is there a special
function I need to try?

Note: I noticed that the data type of the field storing the date is
"text" and not "date/time." This table is part of a proprietary
database so I can't go in and modify the data type. I have a feeling
this is what is causing the problem but since I'm a beginner I'm not
entirely sure.

any ideas? thanks,
Tim

re: date sort in Access 97

Tim,

Use the CDate() function to convert the text field to something Access
recognizes as a date. In other words, instead of sorting on the [Field1]
field, you need to sort on CDate([Field1]).

-Todd Matson


"Tim Lang" <timl@ecmadison.com> wrote in message
news:a88f8822.0404131207.4e1e13ed@posting.google.c om...[color=blue]
> I'm trying to sort results of a query of a number of fields from two
> tables by a date field in one of them. However, it keeps sorting the
> date based only on the first number ("day") of the date (ex. 1/1/2004,
> 2/1/2003, 3/1/2004...) and not the month or year. Is there a special
> function I need to try?
>
> Note: I noticed that the data type of the field storing the date is
> "text" and not "date/time." This table is part of a proprietary
> database so I can't go in and modify the data type. I have a feeling
> this is what is causing the problem but since I'm a beginner I'm not
> entirely sure.
>
> any ideas? thanks,
> Tim[/color]

re: date sort in Access 97

"Todd Matson" <v@x.z> wrote in message news:<SYXec.6835$c%3.344@okepread02>...[color=blue]
> Tim,
>
> Use the CDate() function to convert the text field to something Access
> recognizes as a date. In other words, instead of sorting on the [Field1]
> field, you need to sort on CDate([Field1]).
>[/color]

Thanks, that worked after I figured out how to set up the column. I'm
now trying to make the ascending sort work if there is any "null" data
in that field in the database records. I'd like it to show the record
on the report but if I include nulls the query comes back with an
error about nulls. Any ideas?

Tim