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.

To expand on my 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.

[ajetrumpet]

there is no way to do it if you have nothing to serve as a point of consistency. if the dates are all of different format like that, none of the date functions will work (I don't think anyway). there is no way for access to know if, for example "2009" by itself is a date or not. and if you used the DATEPART function on it, you might get a fraction of the number back. might try that first though