Adding numeric values which may have nulls

[Sashanan]

In an Access 2000 report, I am trying to add four numeric values from four different text fields in a "total" field. Problem is, depending on the criteria set by the user when the report is generated, one or more of these fields may be empty.

Was kind of hoping that adding 20 + 30 + [null] + 50 would just equal 100, but no luck. Any nulls in the calculation prevent it from functioning.

How would I go about having it read '0' for any empty text fields it comes across?
"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)

 

[CosmoKramer]

Check out Access help for the Nz function.

=Nz([Field1]) + Nz([Field2])....etc.

 

[Sashanan]

I've been playing around with this for a couple of hours now but can't seem to produce any results. Must be using the Nz function incorrectly, because it doesn't actually seem to do anything.

My expression is supposed to add the totals of four fields on four different subreports into a 'grand total' text field on the main report. My original expression was like this:

=[SubRep1].MainRep!FieldOnSR1 + [SubRep2].MainRep!FieldOnSR2 + [SubRep3].MainRep!FieldOnSR3 + [SubRep4].MainRep!FieldOnSR4

Works fine as long as the four fields referenced all have a value in them, but when one or more of them don't, I get an #Error result.

So, I've now tried to use the Nz function to counter that, and my expression is now:

=Nz([SubRep1].MainRep!FieldOnSR1;0) + Nz([SubRep2].MainRep!FieldOnSR2;0) + et cetera

No good. Still getting the exact same problem.

Am I getting the syntax wrong here (don't think so because otherwise it should reject my expression outright), using Nz in the wrong place (can't be used in a report, perhaps?), or making some other mistake? I'm pretty much stumped here.

Thanks in advance for any additional help.

"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)

 

[Sashanan]

I have by now worked around the problem by just coding it out in a VBA function - trying to add up the total fields that didn't show up did result in an error, but a simple 'On Error Resume Next' did exactly what I want: count 0 if there was no value to work with, and continue the calculation.

Thanks to all who spent some time on my behalf!


"Much that I bound, I could not free. Much that I freed returned to me."
(Lee Wilson Dodd)