Convert string to date

[Leesha]

Hi,
I have a column in a query that contains dates that are in string format. IE 20111228. I need it to in short date format IE 12/28/2011 so I can filter it by date range.
Thanks,
Leesha

[HansV]

Let's say the column is named TextDate. The following calculated column in the query will convert it to a "real" date value:

RealDate: CDate(Format([TextDate],"0000/00/00"))

[Leesha]

That is so cool! Tell me how "0000/00/00 came out as mm/dd/yyyy? I totally expected it to come out as yyyy/dd/mm and then would need to convert it to mm/dd/yyyy.

Thanks so much,
Leesha

[HansV]

Format([TextDate],"0000/00/00") *does* format the date as yyyy/mm/dd, but Windows is smart enough to recognize yyyy/mm/dd as a valid date format. CDate then converts it to a real date value.