Sum field with some null values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know this is pretty basic but help please. How do I sum a range of fields
in a report when some of them are null? My underlying table has the fields
as numbers but I don't want to see a whole bunch of zero's so I did a iif 0
make null, now it won't total across because some fields are blank. Can I
get around this? Thanks
 
If you are trying to get a horizontal total across Field1 and Field2 and
Field3, use Nz() to specify zero for null, i.e.:
=Nz([Field1],0) + Nz([Field2],0) + Nz([Field3],0)
 
Wonderful, is there anywhere there is a dictionary of variables?

Allen Browne said:
If you are trying to get a horizontal total across Field1 and Field2 and
Field3, use Nz() to specify zero for null, i.e.:
=Nz([Field1],0) + Nz([Field2],0) + Nz([Field3],0)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Akrt48 said:
I know this is pretty basic but help please. How do I sum a range of
fields
in a report when some of them are null? My underlying table has the
fields
as numbers but I don't want to see a whole bunch of zero's so I did a iif
0
make null, now it won't total across because some fields are blank. Can I
get around this? Thanks
Click to expand...
Click to expand...
 
Not sure what you mean by a dictionary of variables.

If you mean a list of functions, Access 2003 has a good list in the help
file.
Open a code window.
Click the Help icon on the toolbar.
Look under:
Table of Contents
Microsoft Access Visual Basic Reference
Functions


--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Akrt48 said:
Wonderful, is there anywhere there is a dictionary of variables?

Allen Browne said:
If you are trying to get a horizontal total across Field1 and Field2 and
Field3, use Nz() to specify zero for null, i.e.:
=Nz([Field1],0) + Nz([Field2],0) + Nz([Field3],0)

Akrt48 said:
I know this is pretty basic but help please. How do I sum a range of
fields
in a report when some of them are null? My underlying table has the
fields
as numbers but I don't want to see a whole bunch of zero's so I did a
iif
0
make null, now it won't total across because some fields are blank.
Can I
get around this? Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top