Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.


Error when importing Excel with blank dates

Error when importing Excel with blank dates

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.


RE: Error when importing Excel with blank dates

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

---- Andy

There is a great need for a sarcasm font.

RE: Error when importing Excel with blank dates

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.

Hook'D on Access
MS Access MVP 2001-2016

RE: Error when importing Excel with blank dates

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.

RE: Error when importing Excel with blank dates


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

Hook'D on Access
MS Access MVP 2001-2016

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close