jsparker
08-06-2008, 10:12 AM
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
08-06-2008, 10:21 AM
Dividing by zero can also throw the overflow error. Could that be happening?
MSAccessRookie
08-06-2008, 10:21 AM
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
08-06-2008, 10:31 AM
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