Nz(variant[, valueifnull]) - Just thought of this 2 minutes ago, wondering if anyone else has ever considered. it...

[MLH]

A97 HELP shows the proper syntax for using Nz as
Nz(variant[, valueifnull])
I'm wondering what to expect from potential past
misuse I've made. For example, consider the following...

Private Sub Command0_Click()
Dim a As Long, b As String
b = Nz(a, "")
MsgBox b
End Sub

a is clearly not a variant, but the Nz function doesn't
really seem to care too much about that fact. I mean,
I get no errors. But that doesn't mean it couldn't be
causing me a problem.

Granted, I've only thought about this for 2 minutes.
But if its of interest to you and if you have time, I
would appreciate your input. Thx.

[Randy Harris]

"MLH" <CR**@NorthState.net> wrote in message
news:59********************************@4ax.com...

A97 HELP shows the proper syntax for using Nz as
Nz(variant[, valueifnull])
I'm wondering what to expect from potential past
misuse I've made. For example, consider the following...

Private Sub Command0_Click()
Dim a As Long, b As String
b = Nz(a, "")
MsgBox b
End Sub

a is clearly not a variant, but the Nz function doesn't
really seem to care too much about that fact. I mean,
I get no errors. But that doesn't mean it couldn't be
causing me a problem.

Granted, I've only thought about this for 2 minutes.
But if its of interest to you and if you have time, I
would appreciate your input. Thx.

You might want to view that syntax example as meaning there is no point to
putting anything other than a variant in the Nz command. In the little
procedure that you posted above, the Nz is absolutely meaningless. a is
dimmed as a Long. A Long can never, under any circumstances, be Null.

Randy

[MLH]

True enough. A variant is the only thing that can be.
Does anyone ever put an expression (in place of variant)
in there - perhaps one that might evaluate to Null...
DLookUp("[UName]","tblMyData","[MyID] = -1") where
[MyID] is an AutoNumber field? Or, would that also be
a misuse of the Nz syntax?

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

You might want to view that syntax example as meaning there is no point to
putting anything other than a variant in the Nz command. In the little
procedure that you posted above, the Nz is absolutely meaningless. a is
dimmed as a Long. A Long can never, under any circumstances, be Null.

Randy

[John Mishefske]

MLH wrote:

True enough. A variant is the only thing that can be.
Does anyone ever put an expression (in place of variant)
in there - perhaps one that might evaluate to Null...
DLookUp("[UName]","tblMyData","[MyID] = -1") where
[MyID] is an AutoNumber field? Or, would that also be
a misuse of the Nz syntax?

DLookup() will return Null if no records match so using Nz() is appropriate with DLookup.

Dim sName As String
sName = Nz(DLookUp("[UName]","tblMyData","[MyID] = -1"), "<NotFound>")

Here if the name is not found the DLookup() returns Null and the Nz() function then
returns "<NotFound>" and that value is assigned to the String variable sName.

--
'---------------
'John Mishefske
'---------------

[david epsom dot com dot au]

> Does anyone ever put an expression (in place of variant)

dim n as long
n = nz(eval("fn()"))

Access 2000, eval converts empty to null, and Nz
without a second parameter converts null back to empty.

(Of course you loose any true null result from the function)

Dim MyFunction(v as variant) as variant
'v is a variant, even if MyFunction was called
'with a string or long parameter: MyFunction(5)
'you can't go the other way: if v is declared as long
'you can't pass a variant parameter
end function

(david)
"MLH" <CR**@NorthState.net> wrote in message
news:s9********************************@4ax.com...

True enough. A variant is the only thing that can be.
Does anyone ever put an expression (in place of variant)
in there - perhaps one that might evaluate to Null...
DLookUp("[UName]","tblMyData","[MyID] = -1") where
[MyID] is an AutoNumber field? Or, would that also be
a misuse of the Nz syntax?

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

You might want to view that syntax example as meaning there is no point to
putting anything other than a variant in the Nz command. In the little
procedure that you posted above, the Nz is absolutely meaningless. a is
dimmed as a Long. A Long can never, under any circumstances, be Null.

Randy

[Randy Harris]

"MLH" <CR**@NorthState.net> wrote in message
news:s9********************************@4ax.com...

True enough. A variant is the only thing that can be.
Does anyone ever put an expression (in place of variant)
in there - perhaps one that might evaluate to Null...
DLookUp("[UName]","tblMyData","[MyID] = -1") where
[MyID] is an AutoNumber field? Or, would that also be
a misuse of the Nz syntax?

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

You might want to view that syntax example as meaning there is no point toputting anything other than a variant in the Nz command. In the little
procedure that you posted above, the Nz is absolutely meaningless. a is
dimmed as a Long. A Long can never, under any circumstances, be Null.

Randy

Yes. I frequently put DLookUp inside a Nz. Using Nz I can safely assign
the output of a DLookup to a Long or String variable. Otherwise I would
need to assign the output to a variant, then test for Null before assigning
to another datatype.

[MLH]

Many thx to all you folks.
I've found a new toy.
Will begin playing henceforth.

[Bri]

If you think about it you will realize that DLookup MUST be returning
a Variant as it could be returning data from a field of any type.

--
Bri

MLH wrote:

True enough. A variant is the only thing that can be.
Does anyone ever put an expression (in place of variant)
in there - perhaps one that might evaluate to Null...
DLookUp("[UName]","tblMyData","[MyID] = -1") where
[MyID] is an AutoNumber field? Or, would that also be
a misuse of the Nz syntax?

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

You might want to view that syntax example as meaning there is no point to
putting anything other than a variant in the Nz command. In the little
procedure that you posted above, the Nz is absolutely meaningless. a is
dimmed as a Long. A Long can never, under any circumstances, be Null.

Randy

[MLH]

Yeah. That makes sense.
xxxxxxxxxxxxxxxxxxxxxxxxxx
On Fri, 21 Oct 2005 20:32:38 GMT, Bri <no*@here.com> wrote:

If you think about it you will realize that DLookup MUST be returning
a Variant as it could be returning data from a field of any type.