Calculation Problem

[kryten]

Hi everyone,

I have a calculated field on a form which adds together two currency calculated fields from two other forms. This works fine as long as there are values in both of the fields I am adding together, the problem comes when one of the fields has no value. If there is no data in one of the fields then the result is a blank field.

Here is my calculated field:

=[BackOrders].[Form]![OrderSubtotal]+[OrderDetails].[Form]![OrderSubtotal]

Please help!

 

[popeye]

Try this.

go to the properties menu of both of the calculated field boxes on the two forms and set their default values to zero.

that would allow access to add them even if they both are blank, as in zero, it would still have a value to add (0+0) as opposed to adding a number+a blank space.

if this works please respond and let me know. I had a similar problem and that seemed to be the trick.

 

[kryten]

calculation problem continues

Hi Popeye,

Thanks for your reply.

Unfortunately, it didn't work. When the field is calculated on the form £0.00 flashes for just a second and then the field goes blank.

 

[Pat Hartman]

Setting the default of a numeric field to 0 is not necessarily the correct solution (you would have had to run an update query to update existing data anyway).

Use the Nz() function whenever you may run into this problem.

=Nz([BackOrders].[Form]![OrderSubtotal],0) + Nz([OrderDetails].[Form]![OrderSubtotal],0)

 

[kryten]

Calculation problem solved

Thanks Pat,

Worked a treat - Bridge players obviously do know all the tricks!

Thanks again.