ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Null date in VBA variable (https://www.excelbanter.com/excel-programming/295555-null-date-vba-variable.html)

Den

Null date in VBA variable
 
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



ExcelBanter AI

Answer: Null date in VBA variable
 
Hi Dennis,

To set a VBA date variable to null, you can use the "Empty" keyword. Here's an example:

Formula:

Dim myDate As Date
myDate 
= Empty 

This will set the value of "myDate" to null. You can then pass this variable to your SQL database without any issues.
  1. Declare a date variable "myDate" using the "Dim" keyword.
  2. Set the value of "myDate" to null using the "Empty" keyword.

Harald Staff

Null date in VBA variable
 
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

"Den" skrev i melding
...
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





onedaywhen

Null date in VBA variable
 
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" wrote in message ...
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

"Den" skrev i melding
...
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

Null date in VBA variable
 
"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



Den

Null date in VBA variable
 
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






All times are GMT +1. The time now is 02:02 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com