import text field 20080704 (4th of july 2008) as date access 03

[Jay H]

I have a " delimited text file and the date component or field is laid out
YYYYMMDD = "20080704" = 4th of July 2008. How can I get Access 03 to accept
this as a date?

This table will be updated regularly with new downloaded text data so simple
is key.

 

[Klatuu]

Where x is the text value of the date as in "20080704"

=DatesSerial(Left(x,4), Mid(x,5,2), Right(x,2))

 

[Jerry Whittle]

First import the text into a temporary table.

Next use an append query to take the data from the temp table to it's table.

Uses something like below to convert the string to a valid date:

CDate(mid("20080704",5,2) & "/" & mid("20080704",7,2)& "/" &
Left("20080704",4))

or in a query:

TheDate: CDate(mid([DateField],5,2) & "/" & mid([DateField],7,2)& "/" &
Left([DateField],4))

 

[Benjy]

Jay

Provided you are NOT using Access 2007 then you can easily do it by setting
up an import specification.

After you have selected the file to import via the Import Text wizard
(File>Get External Data>Import), click on Advanced to open the import
specifcation window. In the dates, times and numbers box, select "YMD" from
the Date Order drop-down, then tick Four Digit Years, and tick Leading Zeros
in Dates. Then, most importantly, just delete whatever is showing by default
in the Date Delimiter box (in my experience it is normally a slash) so that
the box is blank. Finally, in the Field Information grid at the bottom of the
window, change the DataType for any of your required date fields to Date/Time
using the drop-down.

You don't have to save the spec (but if you are going to be doing this again
it doesn't make sense not to!), so click either OK or Save As and continue
following the steps.

When the file is imported you will find that the dates in your txt/csv file
are now stored in a Date/Time field(s) in the Access table, ready for you to
view and process as a date.

I think that this is much easier than having to use either a temporary table
or to force the date text into a date format using CDate or similar (but it
doesn't work in Access 2007, alas).

Hope this helps

Benjy