ordering oldest date to the newest query

[SpiritedAway]

Hi all,

Trying to run a query to sort through a field called [ArcDate] from oldest date to the newest.

The date format of this column is day/month/yeah

At present - I'm using the "Sort: Descending" in query design however it sorts out this column by day like this;

01/01/2010
02/01/2008
05/02/2011
08/11/2007
14/05/2008
20/02/2012


Can anyone advise on the correct expression to be able to run a query so that the dates are sorted like this:

02/01/2006
14/05/2008
01/06/2008
10/08/2008
08/11/2007
05/12/2007
01/01/2010
05/02/2011
20/02/2012

So its ordered by the full date?

Any help would be greatly appreciated.

Using A2007

 

[plog]

Based on the way it wants to sort, it looks ArcDate is a text field, not a date field. You can either change the type of it in the table to be a date (be sure to make a backup before trying this) or you can build a custom field in your query based on ArcDate that is actually a date. Here's a link to Access functions that you can use to create a date field: http://www.techonthenet.com/access/functions/

 

[spikepl]

or perhaps just do an ORDER BY Cdate([Arcdate])

 

[SpiritedAway]

Hi Plog,

You're right - checked the datatype of ArcDate and its text.

If I change the datatype to date - it should keep the current date format.

I'll backup the database and give it a go. Thanks for your help plog