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

How to sort date column in ms access?

P: 99
I need to sort a field that displays date on a form.This field is a textbox that simply displays a column 'date' from sql database i.e. it is not of type datetime when displayed.I had two ideas in mind; either I can convert the format to yyyy/mm/dd which helps sorting text or I should convert text into date format and then sort it.The problem in the first one is that even if I convert to yyyy/mm/dd I need to display as mm/dd/yyyy and the problem in the latter one is that I do not understand how to sort something of type date.Please suggest something.
Apr 4 '12 #1
Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,489
If it's stored in the field as a date then it will automatically sort correctly. If it isn't, then it should be.
Apr 4 '12 #2

P: 99
I would want it to be sorted in ascending order on click of the header and in descending order on double click.
Apr 4 '12 #3

P: 99
I think for the onclick event of the header I can probably use this:
Expand|Select|Wrap|Line Numbers
  1. Me.OrderBy="Date ASC"
Apr 4 '12 #4

NeoPa
Expert Mod 15k+
P: 31,489
That makes sense. For double-click use :
Expand|Select|Wrap|Line Numbers
  1. Me.OrderBy = "[Date] DESC"
Apr 4 '12 #5

P: 99
Will it be a problem if there are nulls in the column date?I see that most of the values are nulls.I have been trying to apply the above logic somewhere else(not where I actually need this) but I am sceptical about null values as I just tried running a particular query in the query window that converts text into date using dateserial() and it gives error when the values are null.
Apr 9 '12 #6

NeoPa
Expert Mod 15k+
P: 31,489
I don't want to be drawn off-topic, so I'll simply state that Nulls, when sorted, will always be at one end or other of the dataset. It doesn't mean they are excluded from the results (It is sorting rather than filtering after all).

Any questions about working with strings that represent dates are separate from this thread, so will require a new one if you wish to ask them.
Apr 10 '12 #7

Post your reply

Sign in to post your reply or Sign up for a free account.