Matching a date with a range of dates (1 Viewer)

[jvanderw]

This is my problem. In a table called TIME is a DATE field. It has dates from 1/1/1999 through the present. What I need to do is assign each date a number from 1 - 26 in a new field in the TIME table. Each number represents a 2 week chunk of time. These chunks each represent a payperiod (there are 26 pay periods per year so each 2 week chunk will be assigned a number from 1 - 26). I have created another table (called PayPeriod) that has the beggining date, ending date, and pay period number for each chunk. Here is a small sample of what the PayPeriod table looks like:
BeginDate EndDate PayPeriod Year
12/19/1998 01/01/1999 1 1999
01/02/1999 01/15/1999 2 1999
01/16/1999 01/29/1999 3 1999
01/30/1999 02/12/1999 4 1999
02/13/1999 02/26/1999 5 1999
02/27/1999 03/12/1999 6 1999
03/13/1999 03/26/1999 7 1999
NOTE: I will also have to place a year with each date.
The TIME table has multiple columns, but I am only worried about the date, Division, CDSSICK. Here is a sample of that table:
DIVISION DATE CDSSICK
03 21-May-99 0
03 21-May-99 0
01 21-May-99 0
01 21-May-99 0
01 21-May-99 0
The following is the code that I am trying to use to make it work:
Option Compare Database
Option Explicit

Public Function PayPeriod(OurDate)
Dim dbs As Database
Dim rst As Recordset
Dim strQuery As String
Dim CurrentDate As Date

CurrentDate = OurDate

strQuery = "SELECT [PayPeriod].BeginDate,[PayPeriod].EndDate, [PayPeriod].PayPeriod, [PayPeriod].Year FROM [PayPeriod] WHERE ([PayPeriod].BeginDate)< " & CurrentDate & " Or ([PayPeriod].BeginDate)= " & CurrentDate & " And ([PayPeriod].EndDate)> " & CurrentDate & " or ([PayPeriod].EndDate)= " & CurrentDate & ";"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strQuery, dbOpenDynaset)
If rst.EOF Then
PayPeriod = "Didn't Work"
Else
rst.MoveLast
PayPeriod = rst!Period
End If

End Function

Public Function PayPeriodYear(OurDate)
Dim dbs As Database
Dim rst As Recordset
Dim strQuery As String

strQuery = "SELECT [PayPeriod].BeginDate,[PayPeriod].EndDate, [PayPeriod].PayPeriod, [PayPeriod].Year FROM [PayPeriod] WHERE (([PayPeriod].BeginDate)<=" & OurDate & " and ([PayPeriod].EndDate)>=" & OurDate & ");"

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strQuery, dbOpenDynaset)
If rst.EOF Then
PayPeriodYear = "Didn't Work"
Else
rst.MoveLast
PayPeriodYear = rst!Year
End If

End Function

 

[EssexColin]

Hi,
Did you get this question sorted yet?

Colin