Importing simple dates

[TerryEA]

Hi everyone

It's been a while but found myself doing some Access work again. Can anyone help? I just want to import a CSV file which contains a date field.

The program the data is coming from needs to export dates as text in the format "20070201" (1st Feb 2007). When I import into an Access table I lose this field, however I try I configure the date. What do I need to do to successfully import the data and maintain the date in Access? Do I need to add the Time Element? Help would be much appreciated.

Terry

 

[SkipVought]

Hi,

You can import as TEXT, then parse and convert to a date in a query...

ConvDate: DateSerial(Mid([txtDate],1,4),Mid([txtDate],5,2),Mid([txtDate],7,2))

where [txtDate] is like 20070201

Skip,

_[red][/red]

 

[TerryEA]

Hi Skip

Thanks for the speedy reply. So, in looking at the table in Design View, does the field have to be a "text" type, rather than "date" type, when I do the import, and if so at what stage do I revert it back to a "date" type for use in the application? Do I run your suggested query first?

Terry

 

[SkipVought]

"...does the field have to be a "text" type, rather than "date" type..."

20070201 is not a date. It is a STRING of numeric characters. The DateValue for February 1, 2007 is 39114, not that you have to know that specific value, but that's how Access STORES the date. The format that is displayed is just that -- its ONLY a display value.

You must take the imported string, in your inported table, and parse it into it's year, month & day parts and CONVERT these parsed values into a Date Value. You can do that in a make-table query.

Skip,

_[red][/red]

 

[TerryEA]

Hi Skip

Got it. Thanks very much mate. Saved the day!

Terry