 | |
nz for blanks in queries - formatting
comp.databases.ms-access comp.databases.ms-access
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
nz for blanks in queries - formatting -
03-06-2008
, 01:13 PM
#2
| |||
| |||
|
|
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. |
#3
| |||
| |||
|
|
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. |
eg. Nz([Amount])+0
Wayne Gillespie
Gosford NSW Australia
#4
| |||
| |||
|
|
I used the Nz() function in a query to replace blank entries with zero so that a subsequent SUM function wouldn't trip over. |
Quote:
|
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. |
#5
| |||
| |||
|
|
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. |
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.
#6
| |||
| |||
|
|
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... (AT) googlemail (DOT) com> wrote in message news:f97e269a-80ee-4997-bf12-68edb00c1b73 (AT) c33g2000hsd (DOT) 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. |
Thanks for that. Using IIf worked a treat.
All the best,
Franc.
#7
| |||
| |||
|
|
On Fri, 7 Mar 2008 14:37:16 +0900, "Allen Browne" <AllenBro... (AT) SeeSig (DOT) 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 |
Thanks for your solution. It worked great.
All the best,
Franc.
#8
| |||
| |||
|
|
franc sutherland <franc.sutherl... (AT) googlemail (DOT) com> wrote in news:f97e269a- 80ee-4997-bf12-68edb00c1... (AT) c33g2000hsd (DOT) 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. |
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.
#9
| |||
| |||
|
|
On Mar 7, 11:24 am, lyle fairfield <lylef... (AT) yah00 (DOT) ca> wrote: franc sutherland <franc.sutherl... (AT) googlemail (DOT) com> wrote in news:f97e269a- 80ee-4997-bf12-68edb00c1... (AT) c33g2000hsd (DOT) 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. |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.