VBA: insert a null into a date field

[zwheeler]

I have an access database. i am inserting data from an excel worksheet

i have a very long insert (41 columns). in the middle of the pack i have several date fields.
Some of the date fields are null and should be null until the user or batch job updates the access db.

i have defined the following
[CODE}
Dim FirmCommitmentIssued
FirmCommitmentIssued =((Sheets("OD_ASSUMPTIONS").Cells(iRow, iCol + 25).Value))
If Len(FirmCommitmentIssued) = 0 Then
FirmCommitmentIssued =null
End If
[/code]
however, i get an automation error, when this occurs

i know i could write a seperate insert statements and do a check so if null then insert without using this field. but then i would mess up some of the other logic.

is there anyway to insert a null or a default value say 1/1/1900
thanks

[Stewart Ross]

Hi. I suspect that your problem arises not from the null, but from using the Sheets collection without referencing the automation server object you must have defined to run Excel from your Access code.

Within Excel, the application object is implicit - so

SetSomething = Sheets("SomeSheet") .SomeRange

will be interpreted OK. This is not a reliable method to use in application automation, although it can work for some iterations within looping code (giving the appearance that some condition is occurring within the loop which is causing the error).

When using application automation you MUST refer to the automation server object explicitly. If you have defined a variable called objExcel, say, you must be explicit in linking it to your code referring to the Sheets collection:

Dim objExcel as Excel.Application
Set objExcel = New Excel.Application
(...)
(...code to open workbook..)
(...)
FirmCommitmentIssued = objExcel.Sheets("OD_Assumptions").Cells(...)

Beware of using or adapting code generated by the Excel macro recorder, as it does not generate explicit references to the application object, and use of its code without consideration of whether or not it is running within Excel or one of the other Office applications can lead to many frustrating hours of error tracing.

-Stewart