Error when importing Excel with blank dates

[puforee]

MS Access 2013

On a routine basis I import a spreadsheet into my data base. Several of the fields contain dates. In the spreadsheet many of the date fields can be blank. When importing, using the External Data (Import & Link) for excel, I will get errors because of these blank Date fields and Access will exclude importing all records with the blank fields. I have not tried anything in VB yet.

I don't want to use a link to the excel sheet because I would have to do that from several duplicate data bases that I use to spread the load. I have split the DB and the import is on the back end db. Also, the link to the spreadsheet shows #Num for the blank date fields, but it does bring in all records.

Is there a way around this? I need all the records imported even if they have a blank date fields.

Thanks,

 

[Andrzejek]

This link may provide some assistance.
You can always write your own code and be in full control of what needs to happen....


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

If I am concerned about the quality of the data, I import the Excel sheet into an import table that accepts junk values. I then scrub the data with append and update queries.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[puforee]

Thanks to both of you..Andy...I used your link information with success although it added two steps to my import process. Excel - change blank/empty date cells to 1/1/1900. Import. In Access I use an update query to change the fields with 1/1/1900 to Null. Duane I used your idea to clean up the data.

Now I need to run the rest of my data base programs to ensure it still works.

 

[dhookom]

Puforee,

If Andy's suggestion resulted in success, you should click the Great Post! link to identify this thread as being closed successfully.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016