setting a date type field to null

K

KSH

I have defined a variable in my vba code as date. I am trying to set its
value to null but I get an error.

Dim HoldDate as date

Holddate = ""

I have several other fields that are text that are obviously fine, but I am
not sure how to set a default for the date type field.
I need to initialize the field after I write a record out before processing
new records

Any help would be appreciated.

Thanks,
 
F

fredg

I have defined a variable in my vba code as date. I am trying to set its
value to null but I get an error.

Dim HoldDate as date

Holddate = ""

I have several other fields that are text that are obviously fine, but I am
not sure how to set a default for the date type field.
I need to initialize the field after I write a record out before processing
new records

Any help would be appreciated.

Thanks,
Click to expand...

="" is not the same as =Null
"" is a zero length string.

HoldDate = Null
will clear a DateTime control.

SomeTextField = ""
will clear a Text datatype control
 
K

KSH

I had tried = Null first but I got an invalid use of null error, which is why
I tried the zero lenght string.
 
D

Dirk Goldgar

KSH said:
I have defined a variable in my vba code as date. I am trying to set its
value to null but I get an error.

Dim HoldDate as date

Holddate = ""

I have several other fields that are text that are obviously fine, but I
am
not sure how to set a default for the date type field.
I need to initialize the field after I write a record out before
processing
new records
Click to expand...


A Date *variable* cannot be set to Null. A Date *field* can be Null, but a
variable of that type always has a value. The only type of variable that
can Null is the Variant data type.

You talk about setting fields in records. If this variable is supposed to
hold the initial value for a field, and you want that value to be Null, then
declare the variable as Variant instead of Date.
 
T

tonia

KSH said:
I have defined a variable in my vba code as date. I am trying to set its
value to null but I get an error.

Dim HoldDate as date

Holddate = ""

I have several other fields that are text that are obviously fine, but I
am
not sure how to set a default for the date type field.
I need to initialize the field after I write a record out before
processing
new records

Any help would be appreciated.

Thanks,
Click to expand...
 
P

Pat Hartman

As fredg already pointed out - "" and Null are not the same thing. I would
like to carry that further to tell you that you need to be especially
careful with text fields if you use "" to clear them rather than Null. You
could end up with a mixture of values in your table and unless you account
for that, your results will be invalid.

For example, Where yourfield Is Null - will return rows where yourfield is
null but NOT rows where it is a zero-length-string and visa versa.
Where yourfield = "" - will return rows where yourfield is a ZLS but NOT
where it is null.

One common solution is to use
Where Nz(yourfield,"") = "" to find records with either null or ZLS values.

Remember, numeric values (date is a numeric value under the covers) can
NEVER be zero-length-strings because they are NOT strings.

To verify this for yourself create a totals query that counts the rows with
each value if you think you have a table where you have both null and ZLS
values in the same column.

Select yourfield, Count(*) As EachCount
From yourtable
where yourfield Is Null or yourfield = ""
Group by yourfield;
 
Top