Date import from excel to access - type mismatch error

[ketangarg86]

Hi,



So I have a macro in excel which imports data from the excel sheet to a table in access db. Now the excel file has 4 columns which have dates.

- I imported the excel file from access via access so that I can get the heading of the table and the table is created. Then I deleted all the data in the table.
- When the table was imported 2 of the date columns got set as Short/text data type. Dont know why.
- Now, if I leave the data type as it is in Access table, my macro button in EXCEL works fine and imports the data to Access. If I change the data type, in access design, of the two data columns to date type, I get the type mismatch error when I run the import macro button in EXCEL. I am going nuts over this error. I even created a blank table in access and defined data types to all columns which would be imported from excel. but still excel macro button shows the same error.

I checked the format of all the four date columns is date.

I did a lot of hit and try and could it be the case that if access table fields are defined properly, but any of the data columns cells in excel sheet is null/empty, it will show the type mismatch error.

ALTERNATIVELY, is it possible and is there a way, that once the data is imported to access, I can convert the value in the column from short text to date type.

Please help.

Thanks.

[June7]

Bing: Access VBA change field type

Does this help http://answers.microsoft.com/en-us/o...a-e27a0e88f3e9

[ketangarg86]

I tried applying custom format m/d/yyyy;; to the date columns and now import to access from excel works fine but the blank cells are shown as 12:00:00AM in access. In access the columns are the data type they are supposed to be.

Is there any other custom format that would not import time to access and only the date..

Thanks for the link. But I think that applies to data once it has been imported to access. I am trying to make sure data imported to access is correct format already..

[June7]

That thread shows code to change the data type of field, whether or not the field has data. However, I've never tried. If it can work, the data must already be structured as a valid date format (no alphas or other special characters).

[ssanfu]

Don't know if this will help, but I found this on Ken Snell's site:

Avoid DataType Mismatch Errors when Importing Data from an EXCEL File or when Linking to an EXCEL File
http://www.accessmvp.com/kdsnell/EXC...tm#DataTypeErr

[ketangarg86]

Thanks. I tried playing around, now the blank date cells imported from excel show as 12:00:00 AM in access column. Is there a way I can format the Access column to blank it out if it is 12:00:00 AM. I tried using conditional formatting but access report has white/grey in alternate rows for easy viewing for the user. Please help.