Sorting on a date field.

A

Austin Powers

I exported my Outlook calendar to an Access file. the file has field such
as Start date, End date, Description, and categories etc.

I have designed a form that just shows the Start date, End date, Description
and Categories and want to sort the table by Start date. When I step
through the date, in the form the Start date I get the following order:

1/1/2007
1/10/2007
1/24/2007
10/1/2006
10/13/2006

Clearly, this is not ascending date order. How DO I sort by date?
 
J

John Vinson

I have designed a form that just shows the Start date, End date, Description
and Categories and want to sort the table by Start date. When I step
through the date, in the form the Start date I get the following order:

1/1/2007
1/10/2007
1/24/2007
10/1/2006
10/13/2006

Clearly, this is not ascending date order. How DO I sort by date?
Click to expand...

Sounds like it's somehow exported the date into a Text field rather
than a Date/time field.

Either change the datatype of the field to Date/Time; or create a
calculated field in a Query:

SortDate: CDate([Start date])

and sort by it.

John W. Vinson[MVP]
 
A

Austin Powers

You'll have to forgive my naivety here (I only installed Access for the
first time just over an hour ago). I've use the likes of paradox and dBase
before today , but this one (Access) is just dumbfounding me.

How would I use "SortDate: CDate([Start date])"? Typing 'cddate' into the
help system turns up nothing descriptive.


John Vinson said:
I have designed a form that just shows the Start date, End date,
Description
and Categories and want to sort the table by Start date. When I step
through the date, in the form the Start date I get the following order:

1/1/2007
1/10/2007
1/24/2007
10/1/2006
10/13/2006

Clearly, this is not ascending date order. How DO I sort by date?
Click to expand...

Sounds like it's somehow exported the date into a Text field rather
than a Date/time field.

Either change the datatype of the field to Date/Time; or create a
calculated field in a Query:

SortDate: CDate([Start date])

and sort by it.

John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

You'll have to forgive my naivety here (I only installed Access for the
first time just over an hour ago). I've use the likes of paradox and dBase
before today , but this one (Access) is just dumbfounding me.
Click to expand...

Access is a VERY different environment from either Paradox or dBase.
Yes, you'll need to do some "unlearning" and relearning.
How would I use "SortDate: CDate([Start date])"? Typing 'cddate' into the
help system turns up nothing descriptive.
Click to expand...

You slipped in an extra D there... <g>

Actually the Help system is vexing in this regard. There's separate
help files for the Access User Interface (UI) and for VBA - it's the
latter which has help on CDate. Type Ctrl-G to open the VBA editor and
then look for help on CDate.

The way you would use it is to type just literally that:

SortDate: CDate([Start Date])

in a vacant Field cell in the Query definition grid. Select
"Ascending" on the Sort line under this calculated field.

John W. Vinson[MVP]
 
Top