Calulation Help

[anon47]

The code below is giving a over flow error when the attorney cost has a zero balance. I have and Idea why I am getting the error but can't figure it out how to fix it?

Me.PaidAttorneyFee = (Me.AttorneyCost - Me.AttorneyPayments) / (Me.TotalPlus - Me.CCRefundSum - Me.NSFChargeDue)

 

[PHV]

Why not using an If ... Then ... Else ... End If instruction ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya anon47 . . .

For the equation you've shown your overflow error is a [blue]result of the denominator expression resolving to zero (0).[/blue] You have to decide what you want the control to show when this happens! [blue]PHV's[/blue] suggestion allows you to do so.

[blue]=IIf((Me.TotalPlus - Me.CCRefundSum - Me.NSFChargeDue)=0,[purple][b]Null[/b][/purple],(Me.AttorneyCost - Me.AttorneyPayments) / (Me.TotalPlus - Me.CCRefundSum - Me.NSFChargeDue))[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[anon47]

Thanks guys I will try it in the morning and post the results

 

[anon47]

Thanks guys here is what I come up with can you tell me if you for see any flaws in the calulation?

Me.PaidAttorneyFee = IIf(IsNull(Me.AttorneyCost - Me.AttorneyPayments), 0, Null) / IIf(IsNull(Me.TotalPlus - Me.CCRefundSum - Me.NSFChargeDue), 0, Null)

 

[VRoscioli]

I do not think this will function as you desire. I suggest you look more carefully at the documentation for Iif()


-V

 

[SgtJarrow]

I am pretty sure that an IIF statement will evaluate all parts before returning an answer. So even if you use the IIF eval to determine if the denominator is 0, this will still fail.

You would need to do a complete If-Then statement here...

IF (Me.TotalPlus - Me.CCRefundSum - Me.NSFChargeDue) = 0 THEN
    Me.PaidAttorneyFee = 0
ELSE
    Me.PaidAttorneyFee = ((Me.AttorneyCost - Me.AttorneyPayments) / (Me.TotalPlus - Me.CCRefundSum - Me.NSFChargeDue))
END IF

And you will see I set the first value to 0 because in most cases you cannot mix NULL and numeric value, unless you defined the value as Variant and that is a bit resource waste.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[anon47]

Thanks guys I think I got it working write now sometimes its hard to get your mind wraped around it lOL

If Me.AddFee = True And (Me.SuitAmount) < 6750.005 Then
If Me.TotalPlus < 0.0049999999 Then
Me.PaidAttorneyFee = 0
Else
Me.PaidAttorneyFee = (Me.AttorneyCost - Me.AttorneyPayments) / (Me.TotalPlus - Me.CCRefundSum - Me.NSFChargeDue)
End If
End If