Help w/ Record Sorting by Date...problem

[Guest]

Hello all,

I would greatly appreciate your assistance with the following problem. I am
trying to sort my records chronologically by date in form view. When I click
"sort ascending" or "sort descending," the result is that the records are
sorted by the date field, but from left to right, not using the year as the
first portion of the sort. Here's an example:

7/12/2001
01/03/2001
01/03/2005
02/09/2004

and so on...

How do I get Access to sort by date properly?

Thanks,
Sean

 

[Rick B]

Is the field a date field in your table? It sounds like it might be a text
field, not a date.

 

[Guest]

It sounds like your date may be being stored in a Text field. Check the data
type, and make sure it is set to Date/Time.

 

[John Vinson]

Hello all,

How do I get Access to sort by date properly?

As noted elsethread, Access is sorting the field as Text rather than
as a date. If you're using the Format() function to change the display
of a date/time field... don't; it recasts the value from date to text.

If it IS a text field and for some reason you cannot change it, put a
calculated field in your query:

SortDate: CDate([TextDate])

to convert from the text string to a real date, and sort by this.

John W. Vinson[MVP]

 

[Guest]

Thank you all for your help. It was indeed a case of the wrong data type.
Most appreciated.

Sean