Thank you for being a valued part of the CNET community. As of December 1, 2020, the forums are in read-only format. In early 2021, CNET Forums will no longer be available. We are grateful for the participation and advice you have provided to one another over the years.

Thanks,

CNET Support

General discussion

Report on Access needed to sum 19 values some null

Jul 13, 2005 1:46AM PDT

Having trouble using sum at end of row to add 19 values on my report. Some boxes will be null, can't use 0 because I average on the column and 0 can be a true possibility.
Access won't recognize the null values so while I thought it would ignore the nulls, it simply ignores the whole row. I tried the if but it was too long doing an if for each 19. It seems simple but I am really confused. Thanks.

Discussion is locked

- Collapse -
Use a loop to fix this
Jul 14, 2005 4:17AM PDT

Basically what you need to do is:

1. Write a bit of vb code to run whenever you want your total to display (this is most likely when your report opens). This assumes your boxes are named "numbox1, numbox2, numbox3 ...numbox19"

It should look something like this

'This sets up the array
boxarray = Array(numbox1, numbox2,...numbox19)

'This nullifies the variable that will be your total
boxtotal = Null

'This For...next loop will automatically loop through your fields and sum the ones that are not null.
For Counter = 1 To 19
If boxarray[Counter].value <> Null Then
'This line adds the value to the total
boxtotal = boxtotal + boxarray[Counter]
End If
'Increment the counter
Counter = Counter + 1
Next
End Sub

This code may not completely correct, it depends on your specific needs, but the principles are there. This basically ignores the fields that are null.

The other way to do it is to setup a form that sums all but the nulls, then passes that value to the report when the report opens. This requires the form to be open as well.

- Collapse -
Use the nz function ...
Jul 23, 2005 8:18PM PDT

to change NULL to 0 for each of the fields to sum.

Basically:
nz(field1,0)+nz(field2,0)+ ... nz(field19,0).

Hope this (still) helps.

Kees