I found out that the variable has to be declared as a "variant" for the null
assignment to work. A date variable will not accept a null assignment.

But this works

Dim mydate as variant.

mydate = null


Dennis


"Harald Staff" wrote in message
...
"onedaywhen" skrev i melding
om...
How do you return a null date in VBA? The value zero in Excel VBA is a
valid date (i.e. 30-Dec-1899)

VBA doesn't support null values, datatypes defaults to 0 or empty strings:

Sub test()
Dim D As Date
MsgBox Format(D, "dddd d.mmm yyyy")
End Sub

The only VBA use I know is with (ADO) recordsets; NULL is a returned value
and ISNULL is a boolean property. Don't know if you can set NULL the same
way, if so it's in the ADO recordset.

I'm not sure how/if you can set a database field to be Null either.
Sure can. NULL or NOT NULL is an essential definition of a SQL-92
column:

CREATE TABLE MyTable
(
ID INTEGER NOT NULL PRIMARY KEY,
LastName VARCHAR(35) NOT NULL,
FirstName VARCHAR(35) NOT NULL,
MiddleName VARCHAR(35) NULL
);

I see. This is in the meaning "null values allowed in the field" / "value
required" ? VBA doesn't do that for himself and his variables, there are
automatic default values generated on declaration.

Best wishes Harald