Can Access take Text and import as Date

  • Thread starter Thread starter GonePostal
  • Start date Start date
G

GonePostal

I have an excel text field that contains a date. It is not pretty, but I can
not control how it is brought into an excel spreadsheet from another source.
Data in the field looks like this:

TH 22-NOV-2007 10:32:21

It contains day of the week, then the date, then the time down to the second
all in one field. How can Access (version 2002) translate this to an
understandable date and time?
 
It's the day of the week that's causing the problem.

One option is to import that as text, and then run an Update query to
translate the text field to a date. Assuming that it's always a 2 character
day of the week, use

CDate(Mid([TextDateField], 3))

Remember that Access has good date-related functionality: you can always get
the day of the week as Format([DateField], "ddd") or Format([DateField],
"dddd"), or Weekday([DateField])
 
One approach would be to import the entire string as text, then use a query
to "parse" it into a date/time value.

This approach allows you to have an (unprocessed) input/import table of data
in the original format, and more-permanent tables that have been well
normalized, and get "filled" via queries.

This is probably a good idea anyway, since Excel/spreadsheet data is
typically not well-normalized. You won't get the best of Access'
relationally-oriented features/functions if you feed it 'sheet data.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
Dear Doug and Jeff,

I will try out this idea. It sounds brilliant! Hopefully it will work. I'll
let you know.
 
Dear Doug and Jeff,

Your solution did the trick. I am working on a European version of Access so
oddly enough every "," must be a ";".

I also had a problem with the reading on the second time. It seemed to be a
software bug and a macro cured it (opening query, saving, closing, and then
running).

Finally, it all worked by putting it in the append query. Thanks fellows! I
was totally unaware of looking at it that way!


--
Sincerely,

GonePostal


Douglas J. Steele said:
It's the day of the week that's causing the problem.

One option is to import that as text, and then run an Update query to
translate the text field to a date. Assuming that it's always a 2 character
day of the week, use

CDate(Mid([TextDateField], 3))

Remember that Access has good date-related functionality: you can always get
the day of the week as Format([DateField], "ddd") or Format([DateField],
"dddd"), or Weekday([DateField])
Click to expand...
 
<picky>
It's not the fact that you're using a European version of Access, but the
fact that your List Separator character (set on the Numbers tab when you go
to Customer Regional Options in the Control Panel) is set to semi-colon
rather than comma.
</picky>

Glad you got it working though.
 
Back
Top