Convert and sort date originally formated as text

[Cagney]

I have a text file that i import and it has a field contaning dates in
the format DDMMMYY as a text string. I would like to convert it to a
date that Access can sort properly. Can anyone offer some help.
Thanks.

 

[Marshall Barton]

I have a text file that i import and it has a field contaning dates in
the format DDMMMYY as a text string. I would like to convert it to a
date that Access can sort properly. Can anyone offer some help.

Try sorting on an expression like:
CDate(Left(textfield,2) & "/" & Mid(textfield, 3, 3) &
"/" & Right(textfield, 2))

 

[John W. Vinson]

I have a text file that i import and it has a field contaning dates in
the format DDMMMYY as a text string. I would like to convert it to a
date that Access can sort properly. Can anyone offer some help.
Thanks.

Sure. Open the table in design view and add a new field, specifying Date/Time
as its datatype. Then run an Update query updating this field to:

CDate(Format([yourtextdate], "@@-@@@-@@"))

This will format the 24JAN11 to 24-JAN-11, which CDate can translate to a
date/time field.

Note that a date/time value is actually stored as a Double Float number, a
count of days and fractions of a day since midnight, December 30, 1899; it can
be formatted any which way, sorted chronologically, etc.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Cagney]

I have a text file that i import and it has a field contaning dates in
the format DDMMMYY as a text string. I would like to convert it to a
date that Access can sort properly. Can anyone offer some help.
Thanks.

Thanks very much to each of you.
Both your options worked like a charm.
Gratefull for your quick responses.
Have a nice day!

 

[David-W-Fenton]

Note that a date/time value is actually stored as a Double Float
number, a count of days and fractions of a day since midnight,
December 30, 1899; it can be formatted any which way, sorted
chronologically, etc.

I've found it useful recently to use the term "string representation
of dates," which is what the formats are. The raw numeric
representation is hard to see, unless you use the "General Number"
format to display it in its raw form.

All the other formats that are human-friendly are string
representations of the underlying date values and when doing
calculations, need to be converted back to the underlying numeric
value. Given that there are so many ways to format the string
representation, and then different ways to interpret it (e.g.,
3/2/2010 is either in February or March, depending on the
interpretation), it's important to realize that whenever you are
doing calculations or writing criteria for a SQL WHERE clause, you
need to use either an unambiguous string representation of the date,
or use a precise date value (using DateSerial() or CDate()).