assigning a null value of a date field to a date variable

[George]

Hi,

At some point in the execution of my program I open a dialog box were the
user is supposed to enter some info. Among other things he is asked to enter
a date in a textbox, but this is optional.

When I try to assign this date to a date variable I get the message “Invalid
use of null†in the case the user has not filled in any date. If I use the Nz
function, a value of 0 is assigned to the date and this later shows as
December 30 1899 which I don’t want.

How can I deal with this situation?

Thanks in advance, George

 

[Allen Browne]

You cannot assign the Null value to a VBA variable of type Date.

You can't assign Null to an Integer either.
Nor a Long, Double, Currency, String, ...

The only VBA data type that can be Null is Variant.
You must therefore use a Variant if it must be able to handle the Null
value.

 

[Klatuu]

Allen is correct.
There are a couple of way to do this. One is to check for a Null value
before populating the date variable. You could juse IsNull to check, but to
ensure a valid date was entered:

If IsDAte(Me.DateControl) Then
dtmDateVariable = Me.DateControl
End If

Another would be to use the Nz function to populate the variable with a
default value:

dtmDateVariable = Nz(Me.DateControl, Date)

Or, you can change the variable data type to Variant.

 

[MDBacker]

How do you change the variable data type to Variant? This if for a form. The
person who created the table, queries and form inbedded a required property
filed on the table, which I was able to change. However, I now receive an
error message stating "tried to assign a null value to a variable that is not
a variant data type. I need to change the data type to a variant data type
b/c the required property is not needed for the form.

It is more of a hassel than anything when the error messages pop up and does
not allow us to imput additional information.

 

[strive4peace]

look in the code that is running at the DIM statement
instead of , for instance
DIM somevariable as date
use
DIM somevariable as variant

but, it would be best to find the line that is causing the problem and
use logic to handle it -- not just change the variable type.

can you post the code that is giving the problem?


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
have an awesome day
*