Null date in VBA variable

[Den]

Given a VBA variable of type date, how can you set the variable to a null
value.

I tried mydate = Null. But VBA does not like that.

Is there someway to set it to null. It is a variable that will be passed
to an SQL database.

Thanks
Dennis

 

[Harald Staff]

Hi Dennis

As soon as you define a variable as a date, it gets the default value zero.
Null is "unknown", while zero is day zero. VBA doesn't really operate with
Null in the database sense of the word.

I'm not sure how/if you can set a database field to be Null either. What's
this for ?

HTH. Best wishes Harald

 

[onedaywhen]

How do you return a null date in VBA? The value zero in Excel VBA is a
valid date (i.e. 30-Dec-1899), even negative numbers are valid (e.g.
-ve 999 is 05-Apr-1897). The usual solution is to use a known date to
signify a null value, preferably one which is implausible in the
context of your app. For example, in a recent project here we used
Newton's birthday to signify a null, however I wouldn't recommend this
because of a double ambiguity (01/04 or 04/01 depending on your
locality or 25/12 depending on calendar system used!)

Harald Staff wrote

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
);

How to set a column's value to null? Definite as NULL and don't put
anything in it! However, even with databases it's recommend to
disallow nulls and specify a default value to use if nothing is put in
the column e.g.

MiddleName VARCHAR(35) NOT NULL DEFAULT '{{NK}}'

--

 

[Harald Staff]

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.

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

 

[Den]

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