Overflow on IIf

[matthewnsarah07]

Me.cupassppe = IIf([cupassppe] < 1, "0", [cupassppe] / [cutotalppe] * 100)

I am using the above in an On Load event on a form - I ma using this code to deal with the possibility that [cupassppe] & [cutotalppe] may both = 0.

However I am still getting an Overflow message - how can i correct this code?

Thanks

 

[DCrake]

Change it be 1 instead of zero as anything divided by 1 is anything and it will prevent the div by zero error

 

[matthewnsarah07]

Not sure what you mean which 0 should i change - I tried altering the True section and it still overflows

 

[DCrake]

You need to test both sides of the equasion before deciding what to do.

You cannot do

0/0*100
or
1/0*100
or
0/1*100

I would create a function to calculate the result

Public Function CalcCupassppe(cupassppe  As Integer, cutotalppe as Intger) As Long

If cupassppe   > 0 And cutotalppe > 0 Then
   CalcCupassppe = (cupassppe/cutotalppe) * 100
Else
   CalcCupassppe = 0
End If

End Function

Then use the following


Me.cupassppe = CutCupassppe(Nz(cupassppe,0),Nz(cutotalppe,0)

David

 

[matthewnsarah07]

User Defined type not defined - is displayed when calling the function as you stated?

Thanks for all your help so far

 

[DCrake]

If you look closely you will see that the word Integer has been spelt incorrectly in the second argument. Revise and retry. This is aircode and as such untested.

David

 

[matthewnsarah07]

Expected variable or procedure not module - now appearing

Was I correct to put this function into a module?

 

[DCrake]

You were correct in putting it in a module but you cannot call the module the same name as the function.

Rename the module to something like ModMyFunctions or ModMain, whatever.

David

 

[matthewnsarah07]

Excellent, all working - many thanks for your help with this

 

[MSAccessRookie]

Me.cupassppe = IIf([COLOR=red][B][cupassppe] < 1[/B][/COLOR], [COLOR=blue][B]"0",[/B][/COLOR] [COLOR=seagreen][B][cupassppe][/B][/COLOR] [COLOR=red][B]/ [cutotalppe][/B][/COLOR] * 100)

I am using the above in an On Load event on a form - I ma using this code to deal with the possibility that [cupassppe] & [cutotalppe] may both = 0.

However I am still getting an Overflow message - how can i correct this code?

Thanks

No one else has pointed these issues out yet, so I will do it.

• If you observe the RED Code above, you will see that I believe that you have tested the incorrect variable in the equation, and need to test cutotalppe instead of cupassppe.
• Since the primary result will be a Number, the override result (0) should also be a Number. You have it as a string. Alternatively, the primary result needs to be converted to a string.