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 |
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:
|
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 |
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 |
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 |
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