Nz function.

[Guest]

Hi gang,

I have a rather lengthy INSERT statement in VBA as part of the code for a
command button. There are date fields as well as variants and currency
fields all being entered.

Some of the fields will be null at the time the code is invoked. I've
played around with the Nz function, and seen that it can only be used with
Variant data types. So, what exactly do I do about the date data type
fields? Do I declare them as Variant in the Dim statement, use the Nz
function and try to convert back to a date? If so, how would that look
exactly?

If the variable in question is dDate, would I have to do something like this?

Dim dDate as string
"insert into table ' " & Nz(dDate,null) & " ';"

or:

"insert into table #" & Nz(dDate,null) & "#;"

As always, thanks for everything.

 

[Wayne Morgan]

"insert into table ' " & Nz(dDate,null) & " ';"

Nz is to replace Null values with something else. Having the value be Null
if it is Null doesn't accomplish much. We will need a better explanation of
what you are trying to accomplish. What is the data type of the field in the
table that you are inserting into? Where are you getting the values for the
insert statement?

 

[Marshall Barton]

I have a rather lengthy INSERT statement in VBA as part of the code for a
command button. There are date fields as well as variants and currency
fields all being entered.

Some of the fields will be null at the time the code is invoked. I've
played around with the Nz function, and seen that it can only be used with
Variant data types. So, what exactly do I do about the date data type
fields? Do I declare them as Variant in the Dim statement, use the Nz
function and try to convert back to a date? If so, how would that look
exactly?

If the variable in question is dDate, would I have to do something like this?

Dim dDate as string
"insert into table ' " & Nz(dDate,null) & " ';"

or:

"insert into table #" & Nz(dDate,null) & "#;"

Since you're using Dim dDate As String, dDate can never be
Null. So, I have to wonder what you are doing to convert a
Null to a date value, or maybe you're not and getting an
error?

If the values are orgininating in control's on a form, then
using Variant is appropriate and, if your regional settings
in Windows have USA date format, you can use:
Nz("#" + dDate + "#", "Null")
OTOH, if the values originate in form controls, there is no
great need to copy the values to variables, so maybe you
have something more complicated going on here.

 

[Guest]

The Nz() function works for any data type. The value Nz returns if the test
value is Null should match the data type of the variable you are putting the
results of the Nz in. It will return a Date data type in this example:
xDate = (zDate,#6/2/2005#)
The problem with date data types, is what is the default if the value is Null?

 

[Guest]

Wayne, Mash, Klatuu (Great movie)

Sorry if I was too vague. This started out as a long insert statement from
a form, and the first error I received was "Invalid use of null". I figured
that was due to the date fields I had defined as "Date". I want to tell the
database that it's OK to have a null value if the form reports it as such,
that why I was trying to use Nz(dDate, null). I was under the impression
that, syntactically, this is what I wanted to try to say to the database.

Marsh, the data is coming from a form, and I was using a variable just to
try to make it slightly more readable.

My intention, as the field in the db to which I'm trying to write are date
fields, was to define the variables as Date and just stick them in the INSERT
statement. There's a possibility that the command button can be invoked at
different time sin the process, so the date fields may or may not be null,
and I wanted the db to reflect that.

 

[Marshall Barton]

Marsh, the data is coming from a form, and I was using a variable just to
try to make it slightly more readable.

My intention, as the field in the db to which I'm trying to write are date
fields, was to define the variables as Date and just stick them in the INSERT
statement. There's a possibility that the command button can be invoked at
different time sin the process, so the date fields may or may not be null,
and I wanted the db to reflect that.

Did you try any of our suggestions? If so, did it work or
what went wrong?

Just to help clarify your thinking on this, don't forget
that you are constructing an SQL statement in a **string**.
This means that the result of the code needs to have literal
values. That's why Nz(dDate, Null) doesn't make any sense.
It would have to be Nz(dDate, "Null"). A similar issue with
placing the value of dDate into the string, where, as Wayne
explained, it must be enclosed in #s and be in USA (or an
unambiguous) format.

 

[Wayne Morgan]

A date/time field in a table will accept a Null value unless you've set the
Required property of the field to Yes. A date/time variable will not accept
a Null value. You can skip the variable and use a direct reference to the
control on the form in the code, use a Variant data type for the variable,
or replace the Null value with a default date.

 

[Guest]

HI guys,

Agin, I greatly appreciate the education. I ended up getting tid of the
variables and using just the control.value.

I guess I just didn't use any common sense with the Nz function. I was
trying to tell the database that it was OK to accept a null by using the
Nz(field, null). I thought using a null default would kinda override the
propensity of the database or variable to reject a null value. Wasn't my
finest day.

Thanks again,
Paul