Type Conversion Failure with dates

[Judy Ward]

Every day I run commands that truncate a table (tblSAR) and import new data
from an Excel worksheet. All of a sudden I am getting a Type Conversion
Failure for the date fields. I can see that some of the dates in the Excel
worksheet are in the format mm/dd/yyyy (with a leading zero) and some are
not. Excel thinks the fields with a leading zero are text fields with a
format of "General". I have tried formatting these fields as Date in Excel
and then performing the import. That did not solve the problem and I can't
figure out why.

The data I get is coming out of another application--I don't have any
control over that. I do have control over formatting the Excel worksheet I
want to import. Linking to the Excel worksheet is not an option. My Primary
Key field has some numeric and some alphanumeric entries and I get an error
when I try linking.

I would sure appreciate any suggestions on what else to try?
Thank you,
Judy

 

[Ken Snell \(MVP\)]

It can be difficult to handle these situations when importing EXCEL data.
But this is a method that should work all the time:

Import the data to a temporary table where the fields are text. Then use an
append query to copy the data from that table to the real table, using
calculated fields in the query to convert the text data to the proper format
for the permanent table.

 

[Judy Ward]

Thank you very much for responding.

Since I have to do this every day and there are thousands of records, I was
looking for the easiest (most automated) way possible. What I ended up doing
was adding code to the Excel macro that is already performing formatting on
the spreadsheet. Multiplying every field of the date columns by the number 1
(using Paste Special) solved the problem. It left the fields that were
already dates alone and turned the fields that were text into date values.
Importing into Access gives the desired results.

 

[Ken Snell \(MVP\)]

Thank you very much for responding.

Since I have to do this every day and there are thousands of records, I
was
looking for the easiest (most automated) way possible.

Doing what I suggested can be done by macro or VBA in ACCESS automatically,
but glad you found a solution that works.

 

[Judy Ward]

If you happen to check back, I still have a question about the method you
suggested. The data that I am importing has 4000+ rows. What is very odd to
me is that a block of these rows (approx 300) has the dates stored as text.
The rest of the rows have the date stored as date. So for each of the date
columns, I have a mixture of dates stored as dates and dates stored as text.

If I do the conversion from text to date in Access, what will happen to the
values that are already date values? I just realized that I can try this out
for myself and see what happens, but if you happen to know I would appreciate
your response.

Thank you,
Judy

 

[Ken Snell \(MVP\)]

My experience is that ACCESS will import the "date-formatted" dates in the
string format corresponding to your Regional Settings for date display.
Then, the append query will need to do a proper conversion from the date
string to a "date" value. If you're in US, no conversion will be needed. But
if you're in a country that uses dd/mm/yyyy format then you'll need to parse
the string to get the correct value for the date in the append query -- and
this is best done using the DateSerial function with the arguments
containing use of Left, Mid, Right, etc. string parsing functions. Not
difficult once you set it up.