My Dates are Zeros

[Michael Conroy]

I am using a recordset to add a new record to my table. All the fields on the
unbound form are assigned to variables with the nz() function around them. I
am assigning "" to strings and 0 to integers should those fields be empty.
The problem occurs with the three date fields. If I use CR =
nz(.copyright,0), the table gets updated with 30 December 1899, which is what
it should do. However, what I want is an empty date field. Is there any way
to have an empty value for a date field? Assigning a "" to a date field gets
me a type mismatch error. I can trap the empty field with if
isnull(.copyright), but then what value do I give the variable CR. I suppose
the solution is to put the isnull statement in with the recordset updating,
but I wanted to find out if a date field can have an empty value. Thanks for
your help.

 

[Michael Conroy]

I found a solution, but the original question still stands. Within the
recordset updating I added
With rst
..addnew
all the other fields
IF CR <> 0 then
!Copyright = CR
end if
..update
end with
So I am guessing that there is no way to add a blank value to a date or
number field. Rather you just don't update those fields. Should I use the
same logic on strings where the length is zero or it equals "" or
vbnullstring? That way I will only update fields with something in it.

 

[Ken Snell MVP]

An "empty" date field would be the value of NULL. So don't use Nz function
in your expression/code. Just use the .copyright reference by itself.

 

[John Spencer MVP]

AND, also, an empty number field has the value NULL. In other words date and
number fields either have a valid date or number value or are null.

The same can be said for string fields ("" is a zero-length string). It
either has a valid string value or it is null.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County