division by zero results in #Error 1

[AccUser]

Is there a formula or If statement that will work in a make-table query to accomplish the following:

Price: Sales / Volume

If either Sales or Volume is zero (0), then the calculated price will be zero (0).

Right now, I am getting #Error or Divide by zero error when executing the query.

I need this to calculate an average price.

Hope you guys can help me on this one.

 

[jebry]

Hi!

There may be a simpler method, but I would probably use the following function:

Global Function AverageSales(Sales as double, Volume as double) as double

If Sale = 0 Or Volume = 0 Then
AverageSales = 0
Else
AverageSales= Sales/Volume
End If

End Function

Then in the field definition of the make-table query use:

AverSales: AverageSales([TableName]!Sales, [TableName]!Volume)

Of course the field, and table names must match yours and the variable names can be whatever you want them to be. The function needs to be in a global module.

hth
Jeff Bridgham

 

[tlbroadbent]

You can use the IIf function in a query in the following manner.

Price: IIf([Volume]=0,0,[Sales]/[Volume]) Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it if you have time.

 

[AccUser]

Have tried the IIf function. Division by zero also occurs if volume was legit but sales was zero.

I think the global function would probably work better because if either field is zero, don't perform the calc.

Thanks to all!