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

[Will]

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

[Allen Browne]

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

[Will]

On Mar 6, 2:43*pm, "Allen Browne" <[e-mail]:

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.