I hope this will help you.
**NOTE: You will have to change the field/table names to your naming scheme.
For testing purposes I created a table. The fields are:
hrsID - autonumber
dteWorked - Date/time
HrsWorked - number - integer
Base - number - base
I entered hours/base hrs for 11 months.
Next I wrote a subroutine to calculate amount of excess hrs from the
previous months. In a standard module (not behind a form), paste this: (watch
for line wrap)
'**********beg code *********
Public Function ExcessCalc(SomeDate As Date) As Integer
Dim strSQL As String
Dim rst As DAO.Recordset
Dim SumExcess As Integer
Dim CurYear As Integer
Dim CurMonth As Integer
ExcessCalc = 0
SumExcess = 0
'change year and month to integer
CurYear = Year(SomeDate)
CurMonth = Month(SomeDate)
'create recordset
strSQL = "SELECT tblHours.HrsWorked, tblHours.Base"
strSQL = strSQL & " FROM tblHours"
strSQL = strSQL & " WHERE Year([dteWorked]) <= " & CurYear & " AND
Month([dteWorked]) < " & CurMonth
strSQL = strSQL & " ORDER BY [dteWorked];"
Set rst = CurrentDb.OpenRecordset(strSQL)
'must check for records
If Not rst.EOF And Not rst.EOF Then
rst.MoveLast
rst.MoveFirst
'yes, there are records
With rst
'loop thru the recordset
Do While Not rst.EOF
'check for worked > base hrs
If !hrsworked > !base Then
'the Mod() function returns excess hrs
' then add to previous hrs
SumExcess = SumExcess + (!hrsworked Mod !base)
Else
'hrs Worked less than base
'subtract diff between base and hrs worked
' if Sum of Excess hrs is greater than zero
' (don't want Excess to go negative)
SumExcess = SumExcess + (!base - !hrsworked) * (SumExcess > 0)
End If
.MoveNext
Loop
End With
End If
'return excess
ExcessCalc = SumExcess
End Function
'**********end code *********
Then I created a query. Paste this in the query in SQL view:
SELECT tblHours.hrsID, tblHours.dteWorked, tblHours.HrsWorked,
tblHours.Base, ExcessCalc([dteWorked]) AS Excess,
ExcessCalc([dteWorked])+[hrsworked] AS MthTot, IIf([MthTot]>=[base],8,0) AS
Vac, IIf([MthTot]>=[base],8,0) AS Sick FROM tblHours ORDER BY
tblHours.dteWorked;
Save the query, then run it. This query would then be the record source for
a form.
Warning: the code and query assumes there is only ONE entry per month for
hours worked.
If you have questions, post back...
HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
Steve,
All months accumilate all of the excess hours for Vacation/Sick hours. So
this way if an employee only works 155hours the next month the excess 10
hours will be credited and will give the employee 8/8hours for Vac/Sick time.
That will leave him a cresit for 5 hours at the end for the next month.
In your scenario,
Month Hours Calculated Excess Vacation Sick
11/2005 165 160 5 8 8
12/2005 165 160 10 8 8
1/2006 155 160 5 8 8
Hope this helps, because I really need it.
Thanks in advance for your time and patients,
Charlie
:
How do you handle these hours:
Month Hours
11/2005 165
12/2005 165
1/2006 155
Do you carry 10 hrs forward to 1/2006 or 5 hrs? If you carry forward 10 hrs,
how many months would you "look back" to do the carry forward?
Your're right, you can't do it just like Excel, but you can write code to
calc the hours to carry forward.
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)
:
Steve,
I have two rows that are in a sub form source is a query;
my columns are month-hours-OT-Vacation-Sick.
on 12/2005 he worked 165 hours that would constitute 8 hrs sick time and 7
hours vacation.
on 1/2006 he worked 155 this would not give him 8 hours sick or 7 hours
vacation.
what I need is to get the 5hrs from 12/2005 and give it to 1/2006.
with excel you could "if(b1>160,b1-160,b1)" but you cant with Access, or
can you?
Hope this helps and Thanks in Advance,
Charlie
:
I'm not really sure what you are looking for, but......
to calc the amount of hours of vacation/sick leave, in an unbound text box, enter
= ([SumOfHoursWorked]\160)*8
Note the back slash - this is integer division.
to calc remaining hours (leftover), use the Mod() function:
= [SumOfHoursWorked] Mod 160
So in your example of 165 hours worked,
= 8 hours vacation/sick leave ((165\160) * 8 = 1 * 8 = 8)
= 5 hours carryover (remainder) (165 Mod 160 = 5)
HTH
--
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)
TwinDad wrote:
All,
I currently have a SubForm that has totals of Hours worked, I need to a
formula that will tell me that for every 160 hours worked they are given 8hrs
Vacation/Sick hours. If I have an employee that worked 165 hours for 1/2006
and can push the 5 hours to the other month (etc.)
My form is based off a Query that Groups/Sums the hours for the month.
Any Ideas?
Thank you in Advance,
