Import Excel and date formats

[thart21]

Hi there,

I'm having a strange issue when importing an Excel 2007 worksheet to Access 2007. I have 4 date fields that are formatted in Excel as Date/Time type *3/14/2001. I have my import process set up by a command button that deletes and appends the new data to the Access table. I have the same Date/Time formatting in the table for all 4 fields.
When I import, 2 of the fields don't come across and the import errors table states Type Conversion Failure.

I don't understand why, if all of the data types are the same, just 2 of the fields would have problems.

Update: I do understand that Access looks at the first several rows to determine the datatype and, in looking closer, these two fields are blank for about the first 20 rows, I put in 0/00/0000 in the first row for both and the data came across. But, since the Excel file data will change (via copy/paste from another source) I don't want my users to have to do this each time. Any other option?

Thanks,

Any ideas??
Thanks!

[ajetrumpet]

so you're appending this data to a table already in access?? why can't you delete the old table and import the excel each time?? If I'm right, I don't believe that blank rows will cause any system errors for data type mismatches if you do it that way. the table should come over one way or another. and after it does, if there are any changes to be made, make them via vba or macros. It shouldn't be tough.