Overflow Error When Running Calculations

[jsparker]

So I have a form broken into three sections.

First section is associate time oriented items (scheduled hours, absent, overtime, available time, production hours, production percentage).

Second section is a set of 5 different environments were time is collected where associates work. (Mode time, % Mode Time, Production Hours in Mode, Documents viewed). Many of the fields in the second section are calculated. Only the Mode Time and Documents viewed is hard coded. Once we have % Mode Time, Production Hours and Docs/Hour it compares the results to see if the Docs/Hour fall within a range. If they meet the range then a Yes/No is checked signifying the associate met the requirements for an incentive within that environemnt/mode then a calculation of potential pay out.

Third section is quality (Number of claims reviewed, errors and a calculated field of quality in percentage form.)

When I click my calculate button everything runs smoothly and provides the totals I need. But if the Scheduled Hours = Absent Hours it runs the calculations for Available Time, Production Hours and Percent Production but then halts and gives me an OverFlow Error. Nothing in the second or 3rd section is completed.

I can post the code if necessary but there is quite a bit with all the calculations and IF/Then statements. Wanted to know if anyone had any thoughts off the top of their head or if I need to move forward and go ahead and post the code? The fields originally were set to Double but I tried Integer and Long Integer from reading in these forums but still receiving the OverFlow error. Something is hanging it up when it gets to the second section and I don't know what.

Thoughts? or should I just post the code?

Thanks.
Joshua

 

[pbaldy]

Dividing by zero can also throw the overflow error. Could that be happening?

 

[MSAccessRookie]

Go ahead and post the code if you can. In all likelihood, you are getting a Division By Zero Error in the calculation on one of your queries. The Nz Function might be able to help you to control that situation.

 

[jsparker]

When I went a different route I was getting a Division by Zero error. How do I work around this? Sometimes there will be zeros when it comes to time (associate is out of the office, etc.) Is there a way to have it skip the environment calculations if there is a zero present so it can calculate the 3rd section without any problems?

I'll post the code but be gentle I've had to teach myself this product.


Private Sub CalculateBonus_Click()
On Error GoTo Err_CalculateBonus_Click

If ScheduledHours = Absent Then
AvailIncentiveTime = 0#
Else: AvailIncentiveTime = (ScheduledHours - Absent - Holiday) + OT
End If

If AvailIncentiveTime = 0# Then
ProductionHours = 0#
Else: ProductionHours = AvailIncentiveTime - ApprovedDownTime - Wait
End If

If ProductionHours = 0# Then
PercentProduction = 0#
End If

If AvailIncentiveTime = ProductionHours And AvailIncentiveTime > 0# Then
PercentProduction = 1
Else: PercentProduction = ProductionHours / AvailIncentiveTime
End If

'Begin Percent Calculations

If OCRModeTime = 0# Then
OCRPerModeTime = 0#
Else: OCRPerModeTime = OCRModeTime / (OCRModeTime + KEModeTime + KEVModeTime + KFIModeTime + BDEModeTime)
End If

If KEModeTime = 0# Then
KEPerModeTime = 0#
Else: KEPerModeTime = KEModeTime / (OCRModeTime + KEModeTime + KEVModeTime + KFIModeTime + BDEModeTime)
End If

If KEVModeTime = 0# Then
KEVPerModeTime = 0#
Else: KEVPerModeTime = KEVModeTime / (OCRModeTime + KEModeTime + KEVModeTime + KFIModeTime + BDEModeTime)
End If

If KFIModeTime = 0# Then
KFIPerModeTime = 0#
Else: KFIPerModeTime = KFIModeTime / (OCRModeTime + KEModeTime + KEVModeTime + KFIModeTime + BDEModeTime)
End If

If BDEModeTime = 0# Then
BDEPerModeTime = 0#
Else: BDEPerModeTime = BDEModeTime / (OCRModeTime + KEModeTime + KEVModeTime + KFIModeTime + BDEModeTime)
End If

'End Percent Calculations


'Begin Prod Calculations

If OCRModeTime = 0# Then
OCRProdTime = 0#
Else: OCRProdTime = (OCRPerModeTime * ProductionHours)
End If

If KEModeTime = 0# Then
KEProdTime = 0#
Else: KEProdTime = (KEPerModeTime * ProductionHours)
End If

If KEVModeTime = 0# Then
KEVProdTime = 0#
Else: KEVProdTime = (KEVPerModeTime * ProductionHours)
End If

If KFIModeTime = 0# Then
KFIProdTime = 0#
Else: KFIProdTime = (KFIPerModeTime * ProductionHours)
End If

If BDEModeTime = 0# Then
BDEProdTime = 0#
Else: BDEProdTime = (BDEPerModeTime * ProductionHours)
End If

'End Prod Calculations

'Begin Docs/Keystrokes Per Hour Calculation

If OCRModeTime = 0# Then
OCRDocsPH = 0#
Else: OCRDocsPH = OCRTotalDocs / (OCRPerModeTime * ProductionHours)
End If

If KEModeTime = 0# Then
KEDocsPH = 0#
Else: KEDocsPH = KETotalDocs / (KEPerModeTime * ProductionHours)
End If

If KEVModeTime = 0# Then
KEVDocsPH = 0#
Else: KEVDocsPH = KEVTotalDocs / (KEVPerModeTime * ProductionHours)
End If

If KFIModeTime = 0# Then
KFIDocsPH = 0#
Else: KFIDocsPH = KFITotalDocs / (KFIPerModeTime * ProductionHours)
End If

If BDEModeTime = 0# Then
BDEDocsPH = 0#
Else: BDEDocsPH = BDETotalDocs / (BDEPerModeTime * ProductionHours)
End If

'End Docs/Keystroks Per Hour Calculations

Mtime = (OCRModeTime + KEModeTime + KEVModeTime + KFIModeTime + BDEModeTime)
If Mtime = 0# Then
PerMtime = 0#
End If
If [OCRProdTime] = 0# And [KEProdTime] = 0# And [KEVProdTime] = 0# And [KFIProdTime] = 0# And [BDEProdTime] = 0# Then
TotalProdTime = 0#
Else: TotalProdTime = ([OCRProdTime] + [KEProdTime] + [KEVProdTime] + [KFIProdTime] + [BDEProdTime])
End If

TotalProdTime = (OCRProdTime + KEProdTime + KEVProdTime + KFIProdTime + BDEProdTime)
PerMtime = (OCRModeTime + KEModeTime + KEVModeTime + KFIModeTime + BDEModeTime) / ProductionHours

'Begin Meet/Exceed Calculations


If OCRDocsPH >= 8855 Or OCRModeTime = 0 Then
[OCR Meet] = -1
End If

If KEDocsPH >= 271 Or KEModeTime = 0 Then
[KE Meet] = -1
End If

If KEVDocsPH >= 271 Or KEVModeTime = 0 Then
[KEV Meet] = -1
End If

If KFIDocsPH >= 37 Or KFIModeTime = 0 Then
[KFI Meet] = -1
End If

If BDEDocsPH >= 24 Or BDEModeTime = 0 Then
[BDE Meet] = -1
End If

'OCR Mode Per Hour Pay Out Potential
If OCRDocsPH > 8855 And OCRDocsPH < 9624.99 Then
OCRPHPO = (OCRPerModeTime * AvailIncentiveTime) * 0
ElseIf OCRDocsPH > 9625 And OCRDocsPH < 10394.99 Then
OCRPHPO = (OCRPerModeTime * AvailIncentiveTime) * 0.33
ElseIf OCRDocsPH > 10395 And OCRDocsPH < 11164.99 Then
OCRPHPO = (OCRPerModeTime * AvailIncentiveTime) * 0.66
ElseIf OCRDocsPH > 11165 And OCRDocsPH < 11934.99 Then
OCRPHPO = (OCRPerModeTime * AvailIncentiveTime) * 0.98
ElseIf OCRDocsPH > 11935 And OCRDocsPH < 12704.99 Then
OCRPHPO = (OCRPerModeTime * AvailIncentiveTime) * 1.31
ElseIf OCRDocsPH > 12705 And OCRDocsPH < 13474.99 Then
OCRPHPO = (OCRPerModeTime * AvailIncentiveTime) * 1.64
ElseIf OCRDocsPH > 13475 And OCRDocsPH < 14244.99 Then
OCRPHPO = (OCRPerModeTime * AvailIncentiveTime) * 1.97
ElseIf OCRDocsPH > 14245 And OCRDocsPH < 15014.99 Then
OCRPHPO = (OCRPerModeTime * AvailIncentiveTime) * 2.3
ElseIf OCRDocsPH > 15015 And OCRDocsPH < 15784.99 Then
OCRPHPO = (OCRPerModeTime * AvailIncentiveTime) * 2.62
ElseIf OCRDocsPH > 15785 And OCRDocsPH < 16554.99 Then
OCRPHPO = (OCRPerModeTime * AvailIncentiveTime) * 2.95
ElseIf OCRDocsPH > 16555 And OCRDocsPH < 17324.99 Then
OCRPHPO = (OCRPerModeTime * AvailIncentiveTime) * 3.28
ElseIf OCRDocsPH > 17325 And OCRDocsPH < 18094.99 Then
OCRPHPO = (OCRPerModeTime * AvailIncentiveTime) * 3.61
ElseIf OCRDocsPH > 18095 Then
OCRPHPO = (OCRPerModeTime * AvailIncentiveTime) * 3.94
ElseIf OCRDocsPH < 8855 Then
OCRPHPO = 0#
End If
'End OCR Mode Per Hour Pay Out Potential

'Begin KE Mode Per Hour Pay Out Potential
If KEDocsPH > 271 And KEDocsPH < 293.99 Then
KEPHPO = (KEPerModeTime * AvailIncentiveTime) * 0
ElseIf KEDocsPH > 294 And KEDocsPH < 317.99 Then
KEPHPO = (KEPerModeTime * AvailIncentiveTime) * 0.33
ElseIf KEDocsPH > 318 And KEDocsPH < 340.99 Then
KEPHPO = (KEPerModeTime * AvailIncentiveTime) * 0.66
ElseIf KEDocsPH > 341 And KEDocsPH < 364.99 Then
KEPHPO = (KEPerModeTime * AvailIncentiveTime) * 0.98
ElseIf KEDocsPH > 365 And KEDocsPH < 387.99 Then
KEPHPO = (KEPerModeTime * AvailIncentiveTime) * 1.31
ElseIf KEDocsPH > 388 And KEDocsPH < 411.99 Then
KEPHPO = (KEPerModeTime * AvailIncentiveTime) * 1.64
ElseIf KEDocsPH > 412 And KEDocsPH < 434.99 Then
KEPHPO = (KEPerModeTime * AvailIncentiveTime) * 1.97
ElseIf KEDocsPH > 435 And KEDocsPH < 458.99 Then
KEPHPO = (KEPerModeTime * AvailIncentiveTime) * 2.3
ElseIf KEDocsPH > 459 And KEDocsPH < 481.99 Then
KEPHPO = (KEPerModeTime * AvailIncentiveTime) * 2.62
ElseIf KEDocsPH > 482 And KEDocsPH < 505.99 Then
KEPHPO = (KEPerModeTime * AvailIncentiveTime) * 2.95
ElseIf KEDocsPH > 506 And KEDocsPH < 528.99 Then
KEPHPO = (KEPerModeTime * AvailIncentiveTime) * 3.28
ElseIf KEDocsPH > 529 And KEDocsPH < 552.99 Then
KEPHPO = (KEPerModeTime * AvailIncentiveTime) * 3.61
ElseIf KEDocsPH > 553 And KEDocsPH < 575.99 Then
KEPHPO = (KEPerModeTime * AvailIncentiveTime) * 3.94
ElseIf KEDocsPH > 576 Then
KEPHPO = (KEPerModeTime * AvailIncentiveTime) * 4.26
ElseIf KEDocsPH < 271 Then
KEPHPO = 0#
End If
' End KE Mode Per Hour Pay Out Potential
'Begin KEV Mode Per Hour Pay Out Potential
If KEVDocsPH > 271 And KEVDocsPH < 293.99 Then
KEVPHPO = (KEVPerModeTime * AvailIncentiveTime) * 0
ElseIf KEVDocsPH > 294 And KEVDocsPH < 317.99 Then
KEVPHPO = (KEVPerModeTime * AvailIncentiveTime) * 0.33
ElseIf KEVDocsPH > 318 And KEVDocsPH < 340.99 Then
KEVPHPO = (KEVPerModeTime * AvailIncentiveTime) * 0.66
ElseIf KEVDocsPH > 341 And KEVDocsPH < 364.99 Then
KEVPHPO = (KEVPerModeTime * AvailIncentiveTime) * 0.98
ElseIf KEVDocsPH > 365 And KEVDocsPH < 387.99 Then
KEVPHPO = (KEVPerModeTime * AvailIncentiveTime) * 1.31
ElseIf KEVDocsPH > 388 And KEVDocsPH < 411.99 Then
KEVPHPO = (KEVPerModeTime * AvailIncentiveTime) * 1.64
ElseIf KEVDocsPH > 412 And KEVDocsPH < 434.99 Then
KEVPHPO = (KEVPerModeTime * AvailIncentiveTime) * 1.97
ElseIf KEVDocsPH > 435 And KEVDocsPH < 458.99 Then
KEVPHPO = (KEVPerModeTime * AvailIncentiveTime) * 2.3
ElseIf KEVDocsPH > 459 And KEVDocsPH < 481.99 Then
KEVPHPO = (KEVPerModeTime * AvailIncentiveTime) * 2.62
ElseIf KEVDocsPH > 482 And KEVDocsPH < 505.99 Then
KEVPHPO = (KEVPerModeTime * AvailIncentiveTime) * 2.95
ElseIf KEVDocsPH > 506 And KEVDocsPH < 528.99 Then
KEVPHPO = (KEVPerModeTime * AvailIncentiveTime) * 3.28
ElseIf KEVDocsPH > 529 And KEVDocsPH < 552.99 Then
KEVPHPO = (KEVPerModeTime * AvailIncentiveTime) * 3.61
ElseIf KEVDocsPH > 553 And KEVDocsPH < 575.99 Then
KEVPHPO = (KEVPerModeTime * AvailIncentiveTime) * 3.94
ElseIf KEVDocsPH > 576 Then
KEVPHPO = (KEVPerModeTime * AvailIncentiveTime) * 4.26
ElseIf KEVDocsPH < 271 Then
KEVPHPO = 0#

End If
'End KEV Mode Per Hour Pay Out Potential
' Begin KFI Mode Per Hour Pay Out Potential
If KFIDocsPH > 37 And KFIDocsPH < 39.99 Then
KFIPHPO = (KFIPerModeTime * AvailIncentiveTime) * 0
ElseIf KFIDocsPH > 40 And KFIDocsPH < 42.99 Then
KFIPHPO = (KFIPerModeTime * AvailIncentiveTime) * 0.33
ElseIf KFIDocsPH > 43 And KFIDocsPH < 46.99 Then
KFIPHPO = (KFIPerModeTime * AvailIncentiveTime) * 0.66
ElseIf KFIDocsPH > 47 And KFIDocsPH < 49.99 Then
KFIPHPO = (KFIPerModeTime * AvailIncentiveTime) * 0.98
ElseIf KFIDocsPH > 50 And KFIDocsPH < 52.99 Then
KFIPHPO = (KFIPerModeTime * AvailIncentiveTime) * 1.31
ElseIf KFIDocsPH > 53 And KFIDocsPH < 55.99 Then
KFIPHPO = (KFIPerModeTime * AvailIncentiveTime) * 1.64
ElseIf KFIDocsPH > 56 And KFIDocsPH < 59.99 Then
KFIPHPO = (KFIPerModeTime * AvailIncentiveTime) * 1.97
ElseIf KFIDocsPH > 60 And KFIDocsPH < 62.99 Then
KFIPHPO = (KFIPerModeTime * AvailIncentiveTime) * 2.3
ElseIf KFIDocsPH > 63 And KFIDocsPH < 65.99 Then
KFIPHPO = (KFIPerModeTime * AvailIncentiveTime) * 2.62
ElseIf KFIDocsPH > 66 And KFIDocsPH < 68.99 Then
KFIPHPO = (KFIPerModeTime * AvailIncentiveTime) * 2.95
ElseIf KFIDocsPH > 69 And KFIDocsPH < 71.99 Then
KFIPHPO = (KFIPerModeTime * AvailIncentiveTime) * 3.28
ElseIf KFIDocsPH > 72 And KFIDocsPH < 74.99 Then
KFIPHPO = (KFIPerModeTime * AvailIncentiveTime) * 3.61
ElseIf KFIDocsPH > 75 And KFIDocsPH < 78.99 Then
KFIPHPO = (KFIPerModeTime * AvailIncentiveTime) * 3.94
ElseIf KFIDocsPH > 79 And KFIDocsPH < 81.99 Then
KFIPHPO = (KFIPerModeTime * AvailIncentiveTime) * 4.26
ElseIf KFIDocsPH > 82 And KFIDocsPH < 84.99 Then
KFIPHPO = (KFIPerModeTime * AvailIncentiveTime) * 4.59
ElseIf KFIDocsPH > 85 And KFIDocsPH < 87.99 Then
KFIPHPO = (KFIPerModeTime * AvailIncentiveTime) * 4.92
ElseIf KFIDocsPH > 88 And KFIDocsPH < 90.99 Then
KFIPHPO = (KFIPerModeTime * AvailIncentiveTime) * 5.25
ElseIf KFIDocsPH > 91 And KFIDocsPH < 94.99 Then
KFIPHPO = (KFIPerModeTime * AvailIncentiveTime) * 5.58
ElseIf KFIDocsPH > 95 Then
KFIPHPO = (KFIPerModeTime * AvailIncentiveTime) * 5.9
ElseIf KFIDocsPH < 37 Then
KFIPHPO = 0#

End If
' End KFI Mode Per Hour Pay Out Potential

' Begin BDE Mode Per Hour Pay Out Potential

If BDEDocsPH > 24 And BDEDocsPH < 24.99 Then
BDEPHPO = (BDEPerModeTime * AvailIncentiveTime) * 0
ElseIf BDEDocsPH > 25 And BDEDocsPH < 26.99 Then
BDEPHPO = (BDEPerModeTime * AvailIncentiveTime) * 0.33
ElseIf BDEDocsPH > 27 And BDEDocsPH < 28.99 Then
BDEPHPO = (BDEPerModeTime * AvailIncentiveTime) * 0.66
ElseIf BDEDocsPH > 29 And BDEDocsPH < 30.99 Then
BDEPHPO = (BDEPerModeTime * AvailIncentiveTime) * 0.98
ElseIf BDEDocsPH > 31 And BDEDocsPH < 32.99 Then
BDEPHPO = (BDEPerModeTime * AvailIncentiveTime) * 1.31
ElseIf BDEDocsPH > 33 And BDEDocsPH < 34.99 Then
BDEPHPO = (BDEPerModeTime * AvailIncentiveTime) * 1.64
ElseIf BDEDocsPH > 35 And BDEDocsPH < 36.99 Then
BDEPHPO = (BDEPerModeTime * AvailIncentiveTime) * 1.97
ElseIf BDEDocsPH > 37 And BDEDocsPH < 38.99 Then
BDEPHPO = (BDEPerModeTime * AvailIncentiveTime) * 2.3
ElseIf BDEDocsPH > 39 And BDEDocsPH < 40.99 Then
BDEPHPO = (BDEPerModeTime * AvailIncentiveTime) * 2.62
ElseIf BDEDocsPH > 41 And BDEDocsPH < 42.99 Then
BDEPHPO = (BDEPerModeTime * AvailIncentiveTime) * 2.95
ElseIf BDEDocsPH > 43 And BDEDocsPH < 44.99 Then
BDEPHPO = (BDEPerModeTime * AvailIncentiveTime) * 3.28
ElseIf BDEDocsPH > 45 And BDEDocsPH < 46.99 Then
BDEPHPO = (BDEPerModeTime * AvailIncentiveTime) * 3.61
ElseIf BDEDocsPH > 47 And BDEDocsPH < 48.99 Then
BDEPHPO = (BDEPerModeTime * AvailIncentiveTime) * 3.94
ElseIf BDEDocsPH > 49 And BDEDocsPH < 50.99 Then
BDEPHPO = (BDEPerModeTime * AvailIncentiveTime) * 4.26
ElseIf BDEDocsPH > 51 And BDEDocsPH < 52.99 Then
BDEPHPO = (BDEPerModeTime * AvailIncentiveTime) * 4.59
ElseIf BDEDocsPH > 53 And BDEDocsPH < 54.99 Then
BDEPHPO = (BDEPerModeTime * AvailIncentiveTime) * 4.92
ElseIf BDEDocsPH > 55 And BDEDocsPH < 56.99 Then
BDEPHPO = (BDEPerModeTime * AvailIncentiveTime) * 5.25
ElseIf BDEDocsPH > 57 And BDEDocsPH < 58.99 Then
BDEPHPO = (BDEPerModeTime * AvailIncentiveTime) * 5.58
ElseIf BDEDocsPH > 59 Then
BDEPHPO = (BDEPerModeTime * AvailIncentiveTime) * 5.9
ElseIf BDEDocsPH < 24 Then
BDEPHPO = 0#
End If

' End BDE Mode Per Hour Pay Out Potential

If [OCR Meet] = -1 And [KE Meet] = -1 And [KEV Meet] = -1 And [KFI Meet] = -1 And [BDE Meet] = -1 Then
TotalModePO = (OCRPHPO + KEPHPO + KEVPHPO + KFIPHPO + BDEPHPO)
Else: TotalModePO = 0#
End If

' IAR

If ClaimsAudited = 0 Then
Quality = 0
Else
Quality = (ClaimsAudited - Errors) / ClaimsAudited
End If

If Quality >= 0.99 And Quality < 0.995 Then
QualityPotPay = 12.5
ElseIf Quality >= 0.995 And Quality < 1 Then
QualityPotPay = 17.5
ElseIf Quality = 1# Then
QualityPotPay = 25#
Else
QualityPotPay = 0
End If

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Exit_CalculateBonus_Click:
Exit Sub
Err_CalculateBonus_Click:
MsgBox Err.Description
Resume Exit_CalculateBonus_Click


End Sub