By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,375 Members | 1,111 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,375 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.
Nov 13 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a

"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

Nov 13 '05 #2

P: n/a
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


Nov 13 '05 #3

P: n/a
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
'---------------
Nov 13 '05 #4

P: n/a
> 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

Nov 13 '05 #5

P: n/a

"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.

Nov 13 '05 #6

P: n/a
MLH
Many thx to all you folks.
I've found a new toy.
Will begin playing henceforth.
Nov 13 '05 #7

P: n/a
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


Nov 13 '05 #8

P: n/a
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.


Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.