How to add fields together

[kmclane]

I thought I understood how to do this. On my report I have a text box for "Equipment Income". The Data for this field comes from a crosstab query that is included in my main query. The data is from anywhere from zero to three fields. In other words, there can be no data. The three fields that can have data are [equipment fee], [equip maint] and [equip purchase]. These can be populated or null. Actually they may be zero length strings as there is no data in the query for some records. I have tried to use "=NZ([Equipment Fee]+[Equipment Maint]+[Equipment Purchase],"$0.00 ")" so that if there was no data I would just get a total. Then I discovered that it wasn't working, so I tried this: "=[Equipment Fee]+[Equipment Maint]+[Equipment Purchase]" just to check that it was adding, but it isn't. I checked on a record I know has a total in one of the three fields. What am I missing this time?
Thanks, Ken.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[kmclane]

I figured it out. You have to NZ each field IN the calculation, that way Access can add. It can add 0.00, but not null.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[kmclane]

Ok, that only works if at least one field is populated, if they are all empty, you get "$0.00$0.00$0.00"
Strange, Any suggestions?
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[dhookom]

Try:
=Val(NZ([Equipment Fee],0))+Val(Nz([Equipment Maint],0))+Val(Nz([Equipment Purchase],0))

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[kmclane]

Thanks, I got it to work this way:

=NZ(NZ([Equipment Fee])+NZ([Equipment Maint])+NZ([Equipment Purchase]),"$0.00 ")

I haven't seen any errors using this yet. Do you know of any potential problems?
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[dhookom]

I never use Nz() without specifying the second argument:
[blue] Nz([NumericFieldName],0)[/blue]
or
[blue] Nz([FirstName],"Not Available")[/blue]

Also, Nz() may return a variant data type so I recommend forcing the value to a numeric using Val() unless you want text.

Also, I would not use "$0.00 " when it seems that you should use just 0 with no quotes or formatting. Formatting should be done in the Format property of the text box in the report.

For these reasons, I would prefer the method that I suggested earlier.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[kmclane]

Ok, I took your advice and changed it. Works fine . Thanks, Ken.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg