Date Sorting

[sfoot0309]

I have the following information in my database.

Disc Name Date1 Description
Name1 yyyy text
Name1 text
Name1 mm/dd/yyyy text
Name1 mm/yyyy text
Name2 mm/dd/yyyy text
Name2 text
etc

The date1 field is a text format and not a date format due to the different date1.

I need to sort by disc name then by the date1 field - in chronological order.

If it can't be done, please let me know.

Thank you in advance.

 

[raskew]

Hi -

Welcome to the forum.

Consider the DateValue() function to convert your text date
to a true date which you can then sort on. Example:

x = "4/24/10"
y = datevalue(x)
? y 
4/24/2010 

[I]To show that y is in fact in numerical date format
as stored by Access.[/I]
? cdbl(y)
 40292

HTH - Bob

ADDED:

If Name1 yyyy text indicates that only a year, e.g. "2010", is input,
the above won't work. I don't know of anything that will, without
a separate function to convert the year to a complete date.

 

[sfoot0309]

Bob,

Thank you for your post.

Unfortunately, the problem I am having is that I have the following dates in a text field in my table

2009
2/1/2008
6/1997
and no date

Of course, when you sort on text fields I get the following result

no date
2009
2/1/2008
6/1997

Of course I want
no date
6/1997
2/1/2008
2009

I am fairly new to access so my knowledge is pretty limited.

Thank you.