OT Calculation

[kaib]

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

[MMcCarthy]

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:

 
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 ...

Me.otPay = getOTHours([OT_HRS], [HR_RATE], [DOT_RATE], [OT_RATE], WORK_DATE)
 

[kaib]

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:

 
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 ...

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

[blyxx86]

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...])

[blyxx86]

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".

[blyxx86]

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.

[MMcCarthy]

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

[blyxx86]

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. :)

[MMcCarthy]

:) 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

[kaib]

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

[blyxx86]

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

[kaib]

ok but how can get a running total of each emloyee code on the report

[kaib]

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

[kaib]

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

[MMcCarthy]

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