I have a report that extracts data from a table on a job by job basis.  When the data is collected I have to make a number of calculations where a number of fields will contain a currency value, but some will will be Null, (Given how the customer uses the program these must be Null and not 0).

I am using the NZ function to add certain field such that I set the Control for my Totals field to the following formula

=Nz([Pcost1],0)+Nz([Pcost2],0)+Nz([PCost3],0)  etc up to Nz([Pcost20],0)

This works fine if there is a value in PCost 1, 2, 3 etc; but if a field contains Null then the Report fails with Type? as the error message in the totals field.

I have tried changing the ,0 to a value such as 99 but it makes no difference.

In desparation, I set up another field FRED where its value was simply Nz([Pcost1],0) and if PCost1 is Null then FRED is Null and not 1.

This is driving me crazy as I'm following the rules, (even using the built in Wizzard), that Microsoft specifies.

If anyone can help with this I'd be very grateful.

---

Reply from Richard Rost:

That should work. Make sure all of the fields are NUMBER types (or Currency) and not TEXT (strings).