Importing blank dates from Excel... (1 Viewer)

MSherfey

Registered User.
Local time
Today, 02:11
Joined
Mar 19, 2009
Messages
103
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

AWF VIP
Local time
Today, 07:11
Joined
Jan 22, 2010
Messages
26,374
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

Registered User.
Local time
Today, 02:11
Joined
Mar 19, 2009
Messages
103
That was it, thank you. I'll just ensure they send actual 'null' fields and not just empty text.
 

Users who are viewing this thread

Top Bottom