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

Syntax for using Nz function to handle blank fields in a querycalculation

P: n/a
Hi,

I need to handle blank values in a query calculation. I have 636
records at the moment but when I sum over these records the blank
fields are not returned. I have looked around here and on Access help
and the Nz function seems to be the way forward.

I am summing over 6 fields, all or none of which may contain values.
The expression syntax I have used is as follows:

BU Weighting SUM: Nz([BU.Weighting],0)+Nz([BU_1.Weighting],
0)+Nz([BU_2.Weighting],0)+Nz([BU_3.Weighting],0)+Nz([BU_4.Weighting],
0)+Nz([BU_5.Weighting],0)

However when I run the query only the 37 records that contain 6 non
Null values in the specified fields are returned. I can't see how my
syntax is incorrect in comparison to the Access Help files.

Any help appreciated.

Thanks,

Will
Mar 6 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
The problem is not with this calculated field. It is with the query itself.

You could verify that by omitting the calculated field, and you would still
only get the same number of records returned.

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

"Will" <wi******@yahoo.comwrote in message
news:72**********************************@f47g2000 hsd.googlegroups.com...
>
I need to handle blank values in a query calculation. I have 636
records at the moment but when I sum over these records the blank
fields are not returned. I have looked around here and on Access help
and the Nz function seems to be the way forward.

I am summing over 6 fields, all or none of which may contain values.
The expression syntax I have used is as follows:

BU Weighting SUM: Nz([BU.Weighting],0)+Nz([BU_1.Weighting],
0)+Nz([BU_2.Weighting],0)+Nz([BU_3.Weighting],0)+Nz([BU_4.Weighting],
0)+Nz([BU_5.Weighting],0)

However when I run the query only the 37 records that contain 6 non
Null values in the specified fields are returned. I can't see how my
syntax is incorrect in comparison to the Access Help files.

Any help appreciated.

Thanks,

Will
Mar 6 '08 #2

P: n/a
On Mar 6, 2:43*pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
The problem is not with this calculated field. It is with the query itself..

You could verify that by omitting the calculated field, and you would still
only get the same number of records returned.

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

Thats correct, thanks. Needed to change to an outer join.
Mar 6 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.