Query Expression

  • Thread starter Thread starter ladybug via AccessMonster.com
  • Start date Start date
L

ladybug via AccessMonster.com

I have this expression in a query. If any of the three are "0" I receive an
error. How can I compute this formula so that I do not receive an error?

expression: [CleanOrdersWithin]/([TotalShippedOrders]-[OrdersReqIntervention])
 
Given your formula, if the denominator evaluates to 0, you'll get a "divide
by 0" error. But if it doesn't, I don't understand.

What is the error message you are getting?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I have this expression in a query. If any of the three are "0" I receive an
error. How can I compute this formula so that I do not receive an error?

expression: [CleanOrdersWithin]/([TotalShippedOrders]-[OrdersReqIntervention])
Click to expand...

Hrm. You should be getting an error only if the denominator is zero -
if [CleanOrdersWithin] is zero, and the denominator is nonzero, you
should just get zero as a result with no error.

Might any of the fields be NULL (rather than zero)? What result do you
want to see if [TotalShippedOrders] is equal to
[OrdersReqIntervention]? Dividing by zero is not meaningful in
arithmatic, quite independent of Access!

That said: try

IIF(NZ([TotalShippedOrders]) = NZ([OrdersReqIntervention]), "Cannot
divide by zero", NZ([CleanOrdersWithin]) / (NZ([TotalShippedOrders]) -
NZ([OrdersReqIntervention]))

or whatever you want to see in the divide-by-zero case.

John W. Vinson[MVP]
 
It is always a good idea when you say you are getting an error to post the
error number, the error description, and the line of code where the error is
happening. It helps others help you. In this case, however, it is blatently
honest. You are getting a divide by zero error. Dividing by zero is a
mathmatical impossibility and no computer can do it. It is always a good
practice to test divisors for a zero value before doing the division. Since
I don't know exactly what you want to happen if the divisor evaluates to
zero, the example below will return the undivided value.

[CleanOrdersWithin]/IIf([TotalShippedOrders]-[OrdersReqIntervention] = 0, 1,
([TotalShippedOrders]-[OrdersReqIntervention]))
 
Back
Top