Calculated field on Form will not calculate if a null value is present

[bmac]

I have a simple textbox called TotalCost with the formula =cost1+cost2+cost3+cost4. The cost fields are table fields. The table fields are set with the format as currency.

I have found that if one of the fields does not have any value in it, the formula won't calculate. If I put a 0 [zero] in the cost fields without any data, the formula works.

Example:
Cost1=$70; cost2=$160; cost3=null; cost4=null; TotalCost=[blank]
cost1-$70; cost2=$160; cost3=0; cost4=0; TotalCost=$230

Why doesn't access just consider a null value as 0?

Bill

 

[spenz]

Bill try this one:

formula = nz(cost1+cost2+cost3+cost4)

"this will automatically convert null values into zero."

 

[missinglinq]

Bill try this one:

formula = Nz(cost1+cost2+cost3+cost4 )

"this will automatically convert null values into zero."

Tis isn't going to do it, either, although it's heading in the right direction.

If cost3 is Null, then

cost1+cost2+cost3+cost4

is going to produce a Null results, and so the result from

Nz(cost1+cost2+cost3+cost4 )

will be Zero.

You need to use the Nz() function for each component

formula = Nz(cost1) + Nz(cost2) + Nz(cost3) + Nz(cost4)

which only converts the Null values to zeros, leaving other values as they are for the addition.

 

[bmac]

Thanks. But I must still be doing something wrong as the empty fields still must be populated with a 0.

My formula is....

=nz([Cost1]+[Cost2]+[Cost3]+[Cost4])

Bill

[submitted before I saw the post from Missinglinq - now will try that]

 

[missinglinq]

As to why Access doesn't consider Null to be zero, Null is, in fact, the presences of No Data or No Value!

To avoid this in this kind of situation,you can always assign Zero as the Default Value of these fields.

 

[bmac]

Thanks Guys. It is now working properly. I think I need to study functions some more!

Bill

 

[bmac]

As to why Access doesn't consider Null to be zero, Null is, in fact, the presences of No Data or No Value!

To avoid this in this kind of situation,you can always assign Zero as the Default Value of these fields.

Thanks! Yes, I tried that and it did not have any effect - although since I am a relative newbie (compared to you guys), I may have done something wrong in the properties sheet.

Bill

 

[spenz]

Yes, linq's suggestion is the correct one bmac. You should try that.

 

[jc_unk]

Hey guys. I had the same problem but I used 0 values as defaults as well. But now when I print my report, all the zeros appear. What do I need to do to make the zeros on my report disappear?

 

[missinglinq]

This is really simple, with Conditional Formatting. In Report Design View

1. Hold down <Shift> and Left Click on each Control in turn.
2. In pre-2007, on the Menu go to Format - Conditional Format
3. In 2007 and later, on the Ribbon, click on the Design Tab then Click on the Conditional Icon
4. Under Condition1 use the Down Arrow to select Field Value Is
5. In the next box select Equal To
6. In the third box type in 0
7. Use the Font/Fore Color icon to set the text color to the same as the back color
8. Click OK
   

You're done!

Linq ;0)>

 

[jc_unk]

That worked perfectly; however, it's leaving gaps on the report. I want the rows to fill in with no spaces above or below. Sorry, pretty about these things. Any suggestions?

 

[Pat Hartman]

I rarely use a default of 0. Zero has a meaning and you can't tell a 0 from no entry if that is important to you. It is if you need to average the results.
The average of 3, 0, 3 is 2
The average of 3, null, 3 is 3
So depending on your desired result, you may want the default to be null and if that is the case, you'll need to understand when you need to use the Nz() function.

Just as with Excel, numeric fields have three formats. The first is for positive values, the second for negative values, and the third is for null values. Just use the format property to define them. Separate the three options with semi-colons. This lets you make negative numbers show up in red or in parentheses or both. Search help for Format to see all the options.