Here is the function I promised. To look for holidays, I have a table named
Holidays. There is one record in the table for each non work day. It has two
fields. [Holdate] is a date field that contains the non work day.
[Holdate_Desc] is a text description of the date.
This function determines the "raw" pay day. That is, it determines that the
date is either 1-15 or 16- last day of the month. It then checks the holiday
table to see if the date is a holiday and if it is, it backs the day up by 1.
It continues that until it hits a non holiday. It then looks to see if it is
a Saturday or Sunday and backs the date up to Friday.
The holiday check needs to be before the week end date check because if a
holiday is Monday (a lot are) and it is the 15th or Last Day, it will back it
up to Sunday.
'---------------------------------------------------------------------------------------
' Procedure : GetPayDay
' DateTime : 9/26/2006 09:24
' Author : Dave Hargis
' Purpose : Determines Pay Day for Date Received
' : Assumes Pay Day is always the 15th or Last Day of the month
' : Or the last working day prior to the date received
'---------------------------------------------------------------------------------------
'
Public Function GetPayDay(dtmSomeDay As Date) As Date
Dim dtmPayDay As Date
Dim intYear As Integer
Dim intMonth As Integer
Dim intDay As Integer
Dim intBackDays As Integer
'Load the periods
On Error GoTo GetPayDay_Error
intYear = year(dtmSomeDay)
intMonth = Month(dtmSomeDay)
intDay = Day(dtmSomeDay)
'Determine Pay Period
If intDay <= 15 Then
intDay = 15
Else
intDay = 0
intMonth = intMonth + 1
End If
'Establish Raw Pay Day
dtmPayDay = DateSerial(intYear, intMonth, intDay)
'Adjust for Holidays
Do While Not IsNull(DLookup("[holdate]", "[holidays]", "[holdate] = #" &
dtmPayDay & "#"))
dtmPayDay = DateAdd("d", -1, dtmPayDay)
Loop
'Check for week end dates
Select Case Weekday(dtmPayDay, vbMonday)
Case 6 'Saturday
intBackDays = -1
Case 7 ' Sunday
intBackDays = -2
Case Else 'Weekday
intBackDays = 0
End Select
'Adjust for weekends
dtmPayDay = DateAdd("d", intBackDays, dtmPayDay)
GetPayDay = dtmPayDay
GetPayDay_Exit:
On Error Resume Next
Exit Function
GetPayDay_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure GetPayDay of Module modDateFunctions"
GoTo GetPayDay_Exit
End Function
Richardb said:
Thank you. Please send the function that will calcute the pay periods.
Thanks
Richard
:
Do you have a table in your database that shows pay period end dates? It is
not that difficult to use some date manipulation function to calculate the
last day of the month and, of course, the 15th is static, so it is a no
brainer; however, some paydays shift because of week end dates or holidays.
If you know those rules, you can calculate them, too. If you have a table of
paydays, it will make it a little easier.
Since this query is for a report, all you need to do is include the
payperiod end date in each record and use the Sorting and Grouping feature of
the report writer.
If you need a function that will calculate the pay periods for the 15th and
last day of the month and back the day up to the first working day prior to
that date when it falls on a weekend or holiday, let me know.
:
I have a query that gets all the data on peoples hours worked. Following is
the SQL statement.
SELECT Rep.LastName, Rep.FirstName, Cosmo.StoreNumber,
Cosmo.CallStartDateTime, Cosmo.CallEndDateTime, Cosmo.Hours,
Cosmo.TotalDurationHours
FROM Rep INNER JOIN Cosmo ON Rep.RepId = Cosmo.RepID
ORDER BY Rep.LastName, Cosmo.StoreNumber, Cosmo.CallStartDateTime;
I have all this information in a report. I want to be able to see how many
hours a person works for a payperiod without writing a seperate query for
each period. The payperiods are the 15th and the last day of the month. I
can get totals for the whole year but not payperiods in my report. Not sure
if this should be part of the query or report. Any help would be appreciated.
Richard
