Sorting by date not working

[natesternberg]

Hi guys --

I imported a TSV into an Access table. One of the columns was dates, and I set the corresponding table column to "Date/Time." However, Access seems to be treating the column as a string. For instance, in a query, it sorts the column like: 1/1/2001, 2/1/2001, 1/2/2001, etc. So I guess I have a three questions:

1) (most importantly) How can I make Access treat this column as a date and not a string? Can I convert the column "in place" so that it becomes an actual date?
2) Since I'm effectively importing strings from a text file, are there some date formats that import successfully and some that don't?
3) Does this strike anyone else as buggy? I would expect that if a column is set to Date/Time, Access would either treat its contents like a date, or fail to import the data at all...

 

[John Big Booty]

Have a look at the DateValue() function.

 

[apr pillai]

While importing text files you have the option to review each field (normally marked as General) and set the correct data type for each field before actually transferring data into access table.

In this case you may create a new field and use DateValue() Function (as suggested by Mr. John) or CDate() Function to convert and update into the new field. If found ok you can delete the old field.

 

[natesternberg]

Ack! Due to an error in the source data, some of my dates were coming up as 2009 when they should have been 2010, and I glossed over that when I was eyeballing the sort results. So Access was sorting like this (correctly):

1/1/2009 2/1/2009 1/2/2010

and my brain saw:

1/1/2010 2/1/2010 1/2/2010

So Access was right all along!

As is often the case, the bug was with me, rather than the software!

Still, good to know about CDate() and DataValue(). Thanks guys!