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

nz for blanks in queries - formatting

P: n/a
Hi,

I used the Nz() function in a query to replace blank entries with zero
so that a subsequent SUM function wouldn't trip over. However, it
meant that I couldn't format the resulting number when displayed in a
form. Is this a feature of the Nz() function or am I doing something
wrong.

Thanks,

Franc.
Mar 6 '08 #1
Share this Question
Share on Google+
8 Replies


P: n/a
In queries, IIf() is better than Nz().

Instead of:
Nz([Amount], 0)
use:
IIf([Amount] Is Null, 0, [Amount])
Access will then understand the data type correctly.

The issue is that Nz() returns a Variant, which has a meaningful subtype in
VBA but is not recognised by JET. Consequently JET treats it as text by
default. IIf() does not have this problem.

An alternative is to explicitly typecast the result of Nz(), e.g.:
CCur(Nz([Amount],0))
This is generally less efficient, but sometimes handy.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"franc sutherland" <fr**************@googlemail.comwrote in message
news:f9**********************************@c33g2000 hsd.googlegroups.com...
>
I used the Nz() function in a query to replace blank entries with zero
so that a subsequent SUM function wouldn't trip over. However, it
meant that I couldn't format the resulting number when displayed in a
form. Is this a feature of the Nz() function or am I doing something
wrong.
Mar 7 '08 #2

P: n/a
On Fri, 7 Mar 2008 14:37:16 +0900, "Allen Browne" <Al*********@SeeSig.Invalid>
wrote:
>In queries, IIf() is better than Nz().

Instead of:
Nz([Amount], 0)
use:
IIf([Amount] Is Null, 0, [Amount])
Access will then understand the data type correctly.

The issue is that Nz() returns a Variant, which has a meaningful subtype in
VBA but is not recognised by JET. Consequently JET treats it as text by
default. IIf() does not have this problem.

An alternative is to explicitly typecast the result of Nz(), e.g.:
CCur(Nz([Amount],0))
This is generally less efficient, but sometimes handy.
Another way to typecast is to force a numeric calculation.

eg. Nz([Amount])+0
Wayne Gillespie
Gosford NSW Australia
Mar 7 '08 #3

P: n/a
franc sutherland <fr**************@googlemail.comwrote in news:f97e269a-
80*************************@c33g2000...legrou ps.com:
I used the Nz() function in a query to replace blank entries with zero
so that a subsequent SUM function wouldn't trip over.
Could you tell us of a situation where SUM trips over nulls.
However, it
meant that I couldn't format the resulting number when displayed in a
form. Is this a feature of the Nz() function or am I doing something
wrong.
It's not a feature of the Nz Function.
Mar 7 '08 #4

P: n/a
"Allen Browne" <Al*********@SeeSig.Invalidwrote in
news:47***********************@per-qv1-newsreader-01.iinet.net.au:
In queries, IIf() is better than Nz().

Instead of:
Nz([Amount], 0)
use:
IIf([Amount] Is Null, 0, [Amount])
Access will then understand the data type correctly.

The issue is that Nz() returns a Variant, which has a meaningful
subtype in VBA but is not recognised by JET. Consequently JET treats
it as text by default. IIf() does not have this problem.
What does IIf return? If it returns, say a double, then it might not treat
IIf(True, "A", "B") well at all. If it returns a variant (and the VBA IIF
does return a variant, but we know that the VBA and the JET-SQL IIF are not
identical) then it's likely JET could deal with its return value.
Mar 7 '08 #5

P: n/a
On Mar 7, 5:37 am, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
In queries, IIf() is better than Nz().

Instead of:
Nz([Amount], 0)
use:
IIf([Amount] Is Null, 0, [Amount])
Access will then understand the data type correctly.

The issue is that Nz() returns a Variant, which has a meaningful subtype in
VBA but is not recognised by JET. Consequently JET treats it as text by
default. IIf() does not have this problem.

An alternative is to explicitly typecast the result of Nz(), e.g.:
CCur(Nz([Amount],0))
This is generally less efficient, but sometimes handy.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"franc sutherland" <franc.sutherl...@googlemail.comwrote in message

news:f9**********************************@c33g2000 hsd.googlegroups.com...
I used the Nz() function in a query to replace blank entries with zero
so that a subsequent SUM function wouldn't trip over. However, it
meant that I couldn't format the resulting number when displayed in a
form. Is this a feature of the Nz() function or am I doing something
wrong.
Hi Allen,

Thanks for that. Using IIf worked a treat.

All the best,

Franc.
Mar 7 '08 #6

P: n/a
On Mar 7, 6:10 am, Wayne Gillespie <best...@NOhotmailSPAM.com.au>
wrote:
On Fri, 7 Mar 2008 14:37:16 +0900, "Allen Browne" <AllenBro...@SeeSig.Invalid>
wrote:
In queries, IIf() is better than Nz().
Instead of:
Nz([Amount], 0)
use:
IIf([Amount] Is Null, 0, [Amount])
Access will then understand the data type correctly.
The issue is that Nz() returns a Variant, which has a meaningful subtype in
VBA but is not recognised by JET. Consequently JET treats it as text by
default. IIf() does not have this problem.
An alternative is to explicitly typecast the result of Nz(), e.g.:
CCur(Nz([Amount],0))
This is generally less efficient, but sometimes handy.

Another way to typecast is to force a numeric calculation.

eg. Nz([Amount])+0

Wayne Gillespie
Gosford NSW Australia
Hi Wayne,

Thanks for your solution. It worked great.

All the best,

Franc.
Mar 7 '08 #7

P: n/a
On Mar 7, 11:24 am, lyle fairfield <lylef...@yah00.cawrote:
franc sutherland <franc.sutherl...@googlemail.comwrote in news:f97e269a-
80ee-4997-bf12-68edb00c1...@c33g2000hsd.googlegroups.com:
I used the Nz() function in a query to replace blank entries with zero
so that a subsequent SUM function wouldn't trip over.

Could you tell us of a situation where SUM trips over nulls.
However, it
meant that I couldn't format the resulting number when displayed in a
form. Is this a feature of the Nz() function or am I doing something
wrong.

It's not a feature of the Nz Function.
Hi Lyle,

Sorry, my mistake there. It wasn't using the SUM function within a
query which caused the problem. It was adding the fields together in
a subsequent form which gave '#error'. Does that make more sense?

All the best,

Franc.
Mar 7 '08 #8

P: n/a
franc sutherland <fr**************@googlemail.comwrote in
news:29**********************************@x30g2000 hsd.googlegroups.com:
On Mar 7, 11:24 am, lyle fairfield <lylef...@yah00.cawrote:
>franc sutherland <franc.sutherl...@googlemail.comwrote in
news:f97e269a-
80ee-4997-bf12-68edb00c1...@c33g2000hsd.googlegroups.com:
I used the Nz() function in a query to replace blank entries with
zero so that a subsequent SUM function wouldn't trip over.

Could you tell us of a situation where SUM trips over nulls.
However, it
meant that I couldn't format the resulting number when displayed in
a form. Is this a feature of the Nz() function or am I doing
something wrong.

It's not a feature of the Nz Function.

Hi Lyle,

Sorry, my mistake there. It wasn't using the SUM function within a
query which caused the problem. It was adding the fields together in
a subsequent form which gave '#error'. Does that make more sense?

All the best,

Franc.
Much
Mar 7 '08 #9

This discussion thread is closed

Replies have been disabled for this discussion.