Date fields and null values

[Thomas Kroljic]

All,
I am getting a "Type Mismatch" error on the following code:

Dim mDate As Date

mDate = iif(isnull(rst!datefield1), "", rst!datefield1)

My question is how do I store/initialize a date type field or memory
variable to nothing? if a date field or variable has a value in it, what do
I need to do to initialize the field or variable back to nothing (blank)?

Thank You,
Thomas J. Kroljic

 

[Wayne Morgan]

Set mDate = 0. By default, a date variable has its value equal to the "base
date" used by VBA and Access. Dates/Times are actually defined off of this
base date (Midnight, 30 Dec 1899). The integer portion is the number of days
since this date and the decimal portion is the time of day during that date.
So a value of 1.25 is 6:00 am on 31 Dec 1899. This is also why you can take
a date and add 1 to get the next date.

 

[Jonathan Parminter]

Hi Wayne,

mDate = iif(isnull(rst!datefield1), "", rst!datefield1)

in the line above you are assigning a (null) string to a
date data type

mDate = nz((rst!datefield1), 0)

the nz function above will assign a o (zero) value if the
field has a null value.

Luck
Jonathan

 

[Thomas Kroljic]

Wayne,
Thank you very much for the explanation. Very helpful. I know now what
to do.
Thanks,
Thomas j. Kroljic

 

[Thomas Kroljic]

Jonathan,
Thanks for the explanation on using the NZ function. I wasn't exactly
sure how this function worked. Between your suggestion and Wayne's, I can
resolve the problem.
Thank you,
Thomas j. Kroljic