I need help with a calculated field in a report that sometimes populate in the report and sometimes it doesn't. I created a query and based the calculated field in my report on it. The calculated field is very simple: =([SumOfVASLabor$s]+[SumOfVASTravel$s]+[SumOfVASMaterial]). I double check my source data as well as my query to see if the report should have returned results for my sample sales orders.
I need help with the following formula that should in many cases give me a negative number but isn't calculating correctly. Here is the formula:
First off, are those dollar signs in the name of the fields? Typically I try to only use the 26 upper case, 26 lower case and 10 numbers in the field name with the optional under score (_). Special characters like that tend to cause troubles later on, but it's your call.
I have a question for you: Is it OK if any of the fields in the Nz statement are null? The way you have it setup now is that if any of those 6 fields are null, it returns a zero (remember how Access handles null values in calculations?). So for example if your first two fields are positive, and any of the last 6 are null, it'll return a positive number ([positive] * [positive] * [positive constant] - 0 = [positive]). That's what I was getting at with:
Originally Posted by nckdryr
Just be careful you know what it's doing and if it's ok how to substitute.
Basically, if a field is null, what is the appropriate response? The answer to that is specific to your database and you need to answer that before you can really lay this problem to bed. Cheers!
Can any of these have nulls in them? If they could, then you will need to use many more instances of NZ(). Your NZ function is going to replace the entire right hands side of that formula with a zero if ANY of the fields give back a null.