Handling null values when importing an excel sheet

[BTilson]

I am working on a utility that imports a daily report sent to us by one of our customers. One of the fields in the excel sheet is a date field representing the desired date that we ship the product back to them. However, in this report, some of the values have "00/00/00" instead of an acual date in them. When I run the code to import this spreadsheet, it generates my normal table, but it also generates a conversion error table telling me about all the invalid dates. The normal table that it creates is fine and perfectly usable, but I'd rather it not worry about the conversion errors.

Anyone have any ideas?

Thanks!

Brooks Tilson
Database Development
Tilson Machine, Inc.

 

[randy700]

You probably need to add an IF statement to your code to ignore the records that contain the invalid date field, or to somehow correct it. Something like...

If theDateField = #00/00/00# Then
'do something (or nothing)
Else
'code to import the record
End If


Randy

 

[LARiot]

Try the nz() function. It'll return blank or what you specify if the variable is null:

nz(theDateField) will return "" if it's a null,
nz(theDateField, "What you want") will return "What you want" if it's null.

 

[Crowley16]

how's about using the code docmd.runsql "drop table ~tempErrors1"

as you say, the table is fine and usable...

--------------------
Procrastinate Now!

 

[LARiot]

Oops, those aren't null values. Try Randy's suggestion instead.

 

[LARiot]

Or better yet try the IIf statement: