Importing Text and Dates Together

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am currently working on an access database and importing data from excel
into an access table. In some of the columns, there are dates and access
recognizes them as dates too, but some of the data has written in "N/A,"
which access cannot recognize and identifies them as errors. thus, they
are not included within these columns. Is there any way to fix this
problem without changing the dates or the "N/A"'s in this access table?
For although I could change each one of them individually, that would take
a long time and a change in data type from date/time to text, and the
office would like to use this database to constantly update their files
(i.e. link this database to another access database).
 
I see two options.

1. Change the existing tables to make these fields Text data type. Then use
the IsDate function to find out which ones can be evaluated as a date and
then the CDate function to turn these into dates for calcualtions.

2. Import them into another table first as text. Then use IsDate and CDate
to convert them into valid dates while importing them into the final tables.

Something like the following check a text field (ex_date) and sees if it can
be evaluated as a date. If so it converts it to a date. If not it puts in a
bogus 1/1/1950 date. You could also change the #1/1/1950# to Null instead.

IIf(IsDate([ex_date])=True,CDate([ex_date]),#1/1/1950#)
 
Back
Top