Convert date in text format to short date format

[Leesha]

Hi,

I have column in a query that contains dates in text format and I need to convert them to short date so I can manipulate them. What is the easiest way to do this? Presently the date looks like this

20100121 - text

and I need it to look like 1/12/2010 short date format.

Thanks,
Leesha

[HansV]

You can either calculate the date in a select query (so the date value wouldn't actually be stored, but calculated on the fly each time you open the query), or add a date/time field to the underlying table and populate it using an update query.
In either case, the expression to calculate the date/time value would look like this:

DateSerial(Left([TextField],4),Mid([TextField],5,2),Right([TextField],2))

where TextField is the name of the text column.

Note: I assume that you meant 01/21/2010 instead of 01/12/2010.

[Leesha]

Wow! No wonder I would've never come up with that code!! And yes, I did mean 21 not 12. Too many hours starring at the computer screen.

Thanks!
Leesha