469,626 Members | 1,611 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,626 developers. It's quick & easy.

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
Nov 12 '05 #1
2 2126
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" <ti**@ecmadison.com> wrote in message
news:a8**************************@posting.google.c om...
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

Nov 12 '05 #2
"Todd Matson" <v@x.z> wrote in message news:<SYXec.6835$c%3.344@okepread02>...
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]).


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
Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Craig Bates | last post: by
2 posts views Thread by Tim Lang | last post: by
5 posts views Thread by WindAndWaves | last post: by
12 posts views Thread by Steve Elliott | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.