By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,466 Members | 2,497 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,466 IT Pros & Developers. It's quick & easy.

date sort in Access 97

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
"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.