Run-time error 11 - Division by zero

[Ashfaque]

I tried to calculated 2 text boxes value when there is value in both. But when both are null or any of them is null then I am getting

"Run-time error 11 - Division by zero" for my this line :

Nz(CLng(TxtAccSaudiPercent.Value), 0) = Nz(CLng(TxtSaudiAccounts.Value), 0) * (100 / (Nz(CLng(TxtSaudiAccounts.Value), 0) + Nz(CLng(TxtExpatAccounts.Value), 0)))

What is the solution to come over this ?

Can someone look into.

 

[arnelgp]

you should test the value with If...Else...End If
before doing the calculation.

 

[The_Doc_Man]

If TxtSaudiAccounts is empty or null AND TxtExpatAccouns is ALSO empty or null, the NZ gives you the zero and the 100/... gives you the divide-by zero. The divide by zero is unavoidable if you attempt it.

A few comments:

1. (Simpler comment) - the default property of any control that has a value is .Value so you can omit it. Thus, TxtSaudiAccounts.Value will equal TxtSaudiAccounts because Access VBA will supply the .Value automatically. Less typing, less chance of mistakes.

2. The NZ to the left of the equals sign is not going to work because the left-hand element shouldn't be a function. The conversion and NZ need to be to the RIGHT of the equals sign.

3. Try

TxtAccSaudiPercent = IIF( Nz( TxtSaudiAccounts, 0 ) = 0 AND Nz( TxtExpatAccounts, 0 ) = 0, 
    0, 
    CLng( NZ( TxtSaudiAccounts * 100 / ( Nz(TxtSaudiAccounts, 0) + Nz( TxtExpatAccounts, 0 ) ) ) ) )

 

[Ashfaque]

If TxtSaudiAccounts is empty or null AND TxtExpatAccouns is ALSO empty or null, the NZ gives you the zero and the 100/... gives you the divide-by zero. The divide by zero is unavoidable if you attempt it.

A few comments:

1. (Simpler comment) - the default property of any control that has a value is .Value so you can omit it. Thus, TxtSaudiAccounts.Value will equal TxtSaudiAccounts because Access VBA will supply the .Value automatically. Less typing, less chance of mistakes.

2. The NZ to the left of the equals sign is not going to work because the left-hand element shouldn't be a function. The conversion and NZ need to be to the RIGHT of the equals sign.

3. Try

TxtAccSaudiPercent = IIF( Nz( TxtSaudiAccounts, 0 ) = 0 AND Nz( TxtExpatAccounts, 0 ) = 0,
    0,
    CLng( NZ( TxtSaudiAccounts * 100 / ( Nz(TxtSaudiAccounts, 0) + Nz( TxtExpatAccounts, 0 ) ) ) ) )

Thanks for the useful info.

I tried with your above line but it produces error '6' - Overflow

 

[Ashfaque]

I tried as below but still get same error :

If IsNull(TxtSaudiAccounts.Value) And IsNull(TxtExpatAccounts.Value) Then
TxtSaudiAccounts = 0: TxtExpatAccounts = 0
TxtAccSaudiPercent = 0
ElseIf Not IsNull(TxtSaudiAccounts.Value) And IsNull(TxtExpatAccounts.Value) Then
TxtAccSaudiPercent = 100
ElseIf IsNull(TxtSaudiAccounts.Value) And Not IsNull(TxtExpatAccounts.Value) Then
TxtAccExpatPercent = 100
Else
TxtAccSaudiPercent = TxtSaudiAccounts * (100 / (CLng(TxtSaudiAccounts.Value) + CLng(TxtExpatAccounts.Value)))
TxtAccExpatPercent = TxtExpatAccounts * (100 / (CLng(TxtSaudiAccounts.Value) + CLng(TxtExpatAccounts.Value)))
End If

 

[theDBguy]

Hi. This is how I usually do it.

IIf(Denominator=0, 0, Numerator/IIf(Denominator=0, 1, Denominator))

Hope that makes sense...

 

[The_Doc_Man]

To be honest, Arnel's proposed idea is best. Have a separate If/Then/Else/End If sequence such that if you are going to divide by zero, don't do the divide in the first place.