443,475 Members | 954 Online
Need help? Post your question and get tips & solutions from a community of 443,475 IT Pros & Developers. It's quick & easy.

OT Calculation

 P: 23 i've got a problem for calculating OT in the report for a small payroll system; when an employee worked on a holiday he's qualified for double overtime. so i've got tblholidays and other related tables i tried the follwing argument If Not IsNull(DLookup("[Hol_id]", "tblHolidays", "[Hol_Date] = #" & WORK_DATE & "#")) Then otpay = [OT_HRS] * [HR_RATE] * [DOT_RATE] Else otpay = [OT_HRS] * [HR_RATE] * [OT_RATE] End If End Sub how can i put this in a custom function in access? help appreciated Nov 30 '06 #1
14 Replies

 Expert Mod 10K+ P: 14,534 You can put this code in a function in a module. However, the values in [OT_HRS], [HR_RATE], [DOT_RATE] and [OT_RATE] which I assume are on a form will not be available to the function unless you pass them in as parameters. e.g. Assuming [OT_HRS] has a data type of Integer, WORK_DATE has a date/time data type and the other are currancy. You could try something like the following: Expand|Select|Wrap|Line Numbers   Function getOTHours(otHrs As Integer, hrRate As Currancy, dotRate As Currancy, otRate As Currancy, workDate As Date) As Currancy      If Not IsNull(DLookup("[Hol_id]", "tblHolidays", "[Hol_Date] = #" & workDate & "#")) Then       getOTHours =otHrs * hrRate * dotRate    Else       getOTHours = otHrs * hrRate * otRate    End If   End Function   To call the function ... Expand|Select|Wrap|Line Numbers Me.otPay = getOTHours([OT_HRS], [HR_RATE], [DOT_RATE], [OT_RATE], WORK_DATE)   Dec 1 '06 #2

 P: 23 You can put this code in a function in a module. However, the values in [OT_HRS], [HR_RATE], [DOT_RATE] and [OT_RATE] which I assume are on a form will not be available to the function unless you pass them in as parameters. e.g. Assuming [OT_HRS] has a data type of Integer, WORK_DATE has a date/time data type and the other are currancy. You could try something like the following: Expand|Select|Wrap|Line Numbers   Function getOTHours(otHrs As Integer, hrRate As Currancy, dotRate As Currancy, otRate As Currancy, workDate As Date) As Currancy      If Not IsNull(DLookup("[Hol_id]", "tblHolidays", "[Hol_Date] = #" & workDate & "#")) Then       getOTHours =otHrs * hrRate * dotRate    Else       getOTHours = otHrs * hrRate * otRate    End If   End Function   To call the function ... Expand|Select|Wrap|Line Numbers Me.otPay = getOTHours([OT_HRS], [HR_RATE], [DOT_RATE], [OT_RATE], WORK_DATE)   it works but it seems there are some problems in fractions: given below the output of the report: Report for Worked Hrs / OT Hrs. Employee No# 1 Per Hr. Rate [P] \$2.90 OT Rate [P] \$1.50 Double OT Rate [P] \$2.00 Employee Name ALFORD Worked Dates| Total Hrs.| Worked Hrs.| Regular Hrs. |OT Hrs.| Reg.Pay| OT Pay | Total 01/Nov/2006 | 13.00 | 12.03| 8.00| 4.03| \$23.20| \$23.20| \$46.40 18/Nov/2006 | 15.00| 14.20| 8.00| 6.20| \$23.20| \$26.10| \$49.30 01/nov/2006 is a holiday: when i calculate OT hrs=4.03 Hr.Rate=2.90 Double OT Rate=2 so OT pay=23.37 but report gives me 23.20 18/nov/2006 is a normal day: when i calculate OT hrs=6.20 Hr.Rate=2.90 Double OT Rate=1.50 so OT pay=26.97 but report gives me 26.10 this causes me confusion in table rates are all currency and hrs are converted to decimal and those fields are integer what could be the reason for this diff. help thanks Dec 2 '06 #3

 100+ P: 256 it works but it seems there are some problems in fractions: given below the output of the report: Report for Worked Hrs / OT Hrs. Employee No# 1 Per Hr. Rate [P] \$2.90 OT Rate [P] \$1.50 Double OT Rate [P] \$2.00 Employee Name ALFORD Worked Dates| Total Hrs.| Worked Hrs.| Regular Hrs. |OT Hrs.| Reg.Pay| OT Pay | Total 01/Nov/2006 | 13.00 | 12.03| 8.00| 4.03| \$23.20| \$23.20| \$46.40 18/Nov/2006 | 15.00| 14.20| 8.00| 6.20| \$23.20| \$26.10| \$49.30 01/nov/2006 is a holiday: when i calculate OT hrs=4.03 Hr.Rate=2.90 Double OT Rate=2 so OT pay=23.37 but report gives me 23.20 18/nov/2006 is a normal day: when i calculate OT hrs=6.20 Hr.Rate=2.90 Double OT Rate=1.50 so OT pay=26.97 but report gives me 26.10 this causes me confusion in table rates are all currency and hrs are converted to decimal and those fields are integer what could be the reason for this diff. help thanks M\$ taking the extra money into their pockets. Duh? No, it is rounding the hours (possibly both ways, but example only shows 2 values being rounded down) So instead of being 6.2 hours, it is only calculating 6. I don't know how to fix this error, but I would check decimal places of where the units are being used AS integer (seeing as an integer is a whole number [1,2,3, 0..] not [1.4,1.5...]) Dec 2 '06 #4

 100+ P: 256 M\$ taking the extra money into their pockets. Duh? No, it is rounding the hours (possibly both ways, but example only shows 2 values being rounded down) So instead of being 6.2 hours, it is only calculating 6. I don't know how to fix this error, but I would check decimal places of where the units are being used AS integer (seeing as an integer is a whole number [1,2,3, 0..] not [1.4,1.5...]) Try instead of AS INTEGER using "AS Single". Dec 2 '06 #5

 100+ P: 256 Try instead of AS INTEGER using "AS Single". Ok, one last try here to tell you the right thing.. Try using AS Decimal if Single doesn't work. Byte, Integer and Long Integer do not store decimal values. Single stores up to 7, Double up to 15, and Decimal to 28. Depends on what you need. Decimal may be less confusing because you know the number system 0-9. Dec 2 '06 #6

 Expert Mod 10K+ P: 14,534 Ok, one last try here to tell you the right thing.. Try using AS Decimal if Single doesn't work. Byte, Integer and Long Integer do not store decimal values. Single stores up to 7, Double up to 15, and Decimal to 28. Depends on what you need. Decimal may be less confusing because you know the number system 0-9. blyxx86 is right. Using Single or Double should solve your problem. Mary Dec 2 '06 #7

 100+ P: 256 blyxx86 is right. Using Single or Double should solve your problem. Mary :) See, I'm learning too Mary. :) It feels good to be able to help others when I've been instructed by those who are truly great at Access. I wouldn't be able to construct something like an overtime calculation sheet, but I'm getting there. :) Dec 2 '06 #8

 Expert Mod 10K+ P: 14,534 :) See, I'm learning too Mary. :) It feels good to be able to help others when I've been instructed by those who are truly great at Access. I wouldn't be able to construct something like an overtime calculation sheet, but I'm getting there. :) It's great to see someone who's learned something and wants to impart it to others. Keep up the good work. Mary Dec 3 '06 #9

 P: 23 It's great to see someone who's learned something and wants to impart it to others. Keep up the good work. Mary yes thnks folks single works let me ask u when i view table in datasheet view and click on work_hrs value it displays 12.93333 and when focus goes it's back to 12.93 in the design view work_hrs filed property is set as follows: field size: Single Format : Fixed Decimal : 2 this is one the field there are 2-3 fieds are set like this but in data sheet view if the figure is 12.90 eventhough u click it doesn't change if focus goes these are some basic which i lack so confused thanks for ur efforts Dec 3 '06 #10

 100+ P: 256 yes thnks folks single works let me ask u when i view table in datasheet view and click on work_hrs value it displays 12.93333 and when focus goes it's back to 12.93 in the design view work_hrs filed property is set as follows: field size: Single Format : Fixed Decimal : 2 this is one the field there are 2-3 fieds are set like this but in data sheet view if the figure is 12.90 eventhough u click it doesn't change if focus goes these are some basic which i lack so confused thanks for ur efforts "Fixed" actually will display the numbers as regular numbers. example ____.## it will only display the decimals to the hundredths. press F1 while in the "Format" in design mode for an explanation on the control. Then press "Number and Currency Data Types." it will explain the different values and also how to format you own. :) Sometimes the help is confusing as heck, but other times it can Dec 3 '06 #11

 P: 23 ok but how can get a running total of each emloyee code on the report Dec 3 '06 #12

 P: 23 ok but how can get a running total of each emloyee code on the report ok i got it by changing the 'running sum' property' to 'over group' and it gives the cumulative figure in report i want to print details for only one employee (one employee code) on one page and if its different code it should print on another page is it possible! help appreciated Dec 5 '06 #13

 P: 23 ok but how can get a running total of each emloyee code on the report ok i got it by changing the 'running sum' property' to 'over group' and it gives the cumulative figure Dec 5 '06 #14

 Expert Mod 10K+ P: 14,534 ok i got it by changing the 'running sum' property' to 'over group' and it gives the cumulative figure in report i want to print details for only one employee (one employee code) on one page and if its different code it should print on another page is it possible! help appreciated You will need a group for employee code and in the group header properties set 'Force New Page' to after section. Mary Dec 5 '06 #15