Calculated field in Report returning "error"

[AshikHusein]

I am creating fields in Access Reports which are calculated from other fields in the Report. The problem is that if there is a division by zero, it gives an "error" listing on the report.

I would like to know the code that can be put into the Expression Builder so that if there is an error it should put in 0.00 on the report, for the created field.

I am very new to developing Access databases so any effective suggestions will also be appreciated. Thank you.

 

[suepowell]

I think you probably need to use the iif function

If you have an expression such as [total] = ([value1]/[value2]) where value 2 might be 0 try using the following

[total] = iif(([value2] = 0),0.0,([value1]/[value2])

The first part ([value2] = 0) is an expression which the iif function evaluates to see what to do so put your relavent test condition in here.
the bit after the first comma is what you want to see if the expression is true, in this case 0.0 (if the field is a text field this needs to be "0.0")
the bit after the second comma is what you want to see if the expression is false, in this case [value1]/[value2] .

Hope this helps, you should find iif in the help files if this explanation is not clear.

Sue

 

[AshikHusein]

There is a box asking for a parameter value.

Hi!

I tried the IIF statement. However when I do that a box pops up asking for a parameter value. Also, I should mention here that [value2] is also a calculated field. Any reason why this shld happen and any suggestions as to wht can be done? thanks.

 

[AshikHusein]

Problem solved

I realized what the problem was!! Instead of using IIF I was using IFF. Thanks for your help Sue.

 

[KevinM]

Think Rob's gone home or to bed.
Not sure what time it is over the pond!

He won't mind if I step in and try and help (we have an 'understanding' )

Try....

=[Forms]![main form]![orders].[Form]![ORDER DETAILS].[Form]![txtsubtotal]

or

=[Forms]![main form]![orders]![ORDER DETAILS]![txtsubtotal]

If any work, all credit should go to Rob.
I take no credit whatsoever