Importing blank dates from Excel...

[MSherfey]

I have a decent sized Excel 2007 sheet which contains contract information. One of the fields is the date the customer renewed their contract. If they haven't renewed yet, then the field is blank.

When I try to import this sheet into Access 2007 it gives a conversion error on the rows which had 'blank' dates. I then went and changed all the 'blank' dates to 01/01/1900; now the import it fine.

Is it possible to import a date field which contains blank cells? If so, how? If not, why not ? Finally, is what I did as a work-around the way to go in these instances?

Thanks in advance for all your help!

 

[vbaInet]

Unless your company was established in the 19th century you should be fine

The problem I see here is that those cells aren't actually blank but are zero length string so Access is treating that as an invalid date. To test it in Excel do =IsBlank(CellReference)

If it returns False then you know it isn't blank. Perform a Find/Replace, replace it with 01/01/1900 then replace 01/01/1900 with blank. That will now be regarded as Null instead of zls.

 

[MSherfey]

That was it, thank you. I'll just ensure they send actual 'null' fields and not just empty text.

 

[vbaInet]

You're welcome!