yyyymmdd format??????

[Clay]

Does anyone know how to format a date in access or excel
to accept and treat the date in a text format "yyyymmdd"
as a date?

I import data in text format in access, but I need to
convert the date to a format that access can handle for
doing reports (grouping, etc.)

any ideas?

thanks in advance.
Clay

 

[John Nurick]

Hi Clay,

Import the date to a text field. Then use an calculated field like this
- replacing "TheDate" with the name of your date/time field and "x" with
the name of the text field - in a query to convert it to an Access
date/time value:

TheDate: DateSerial(CInt(Left([x], 4)), CInt(Mid([x], 5, 2)),
CInt(Right([x], 2)))

You can either add a date/time field to the table you have imported, and
use an update query to put the values into it; or if you have imported
to a temporary table, use the expression in the append query you're
using to move the data to the permanent table.