By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,475 Members | 954 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
14 Replies


MMcCarthy
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
  1.  
  2. Function getOTHours(otHrs As Integer, hrRate As Currancy, dotRate As Currancy, otRate As Currancy, workDate As Date) As Currancy
  3.  
  4.    If Not IsNull(DLookup("[Hol_id]", "tblHolidays", "[Hol_Date] = #" & workDate & "#")) Then
  5.       getOTHours =otHrs * hrRate * dotRate
  6.    Else
  7.       getOTHours = otHrs * hrRate * otRate
  8.    End If
  9.  
  10. End Function
  11.  
To call the function ...

Expand|Select|Wrap|Line Numbers
  1. Me.otPay = getOTHours([OT_HRS], [HR_RATE], [DOT_RATE], [OT_RATE], WORK_DATE)
  2.  
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
  1.  
  2. Function getOTHours(otHrs As Integer, hrRate As Currancy, dotRate As Currancy, otRate As Currancy, workDate As Date) As Currancy
  3.  
  4.    If Not IsNull(DLookup("[Hol_id]", "tblHolidays", "[Hol_Date] = #" & workDate & "#")) Then
  5.       getOTHours =otHrs * hrRate * dotRate
  6.    Else
  7.       getOTHours = otHrs * hrRate * otRate
  8.    End If
  9.  
  10. End Function
  11.  
To call the function ...

Expand|Select|Wrap|Line Numbers
  1. Me.otPay = getOTHours([OT_HRS], [HR_RATE], [DOT_RATE], [OT_RATE], WORK_DATE)
  2.  


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

blyxx86
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

blyxx86
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

blyxx86
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

MMcCarthy
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

blyxx86
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

MMcCarthy
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

blyxx86
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

MMcCarthy
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

Post your reply

Sign in to post your reply or Sign up for a free account.