first date and last date of that 2 week pay period

[BBBryan]

Hi,
Can someone help me.
In MsAccess 2007
I have a column with all the dates in order for 2014
I want a query to tell what is the first date and last date of that 2 week pay period.
The 2 week pay period would start on Dec 28 2013 and be consistent for the whole year.
Like this
Date FirstDateOfPayPeriod LastDateOfPayPeriod
Jan 01 2014 Dec 28 2013 Jan 10 2014
Jan 02 2014 Dec 28 2013 Jan 10 2014
Jan 03 2014 ...so on Dec 28 2013 Jan 10 2014
Jan 09 2014 Dec 28 2013 Jan 10 2014
Jan 10 2014 Dec 28 2013 Jan 10 2014
and so on...
Jan 11 2014 Jan 11 2014 Jan 24 2014
Jan 12 2014 Jan 11 2014 Jan 24 2014
Jan 13 2014 ...so on Jan 11 2014 Jan 24 2014
Jan 24 2014 Jan 11 2014 Jan 24 2014
and so on...
Jan 25 2014 Jan 25 2014 Feb 07 2014
Jan 26 2014 Jan 25 2014 Feb 07 2014
Feb 07 2014 ...so on Jan 25 2014 Feb 07 2014

I found a module to do it for the a 1 week period but can't figure out how too do it for a 2 week period.
The module for the 1 week is working off the ComputorSystemCalander on the week start date (Sat,Sun,Mon etc.).
He is the 1 week module

Public Function FirstDayInWeek(Optional dtmDate As Variant) As Date
If IsMissing(dtmDate) Then
dtmDate = Date
End If
FirstDayInWeek = dtmDate - _
Weekday(dtmDate, vbUseSystemDayOfWeek) + 1
End Function

Public Function LastDayInWeek(Optional dtmDate As Variant) As Date
If IsMissing(dtmDate) Then
dtmDate = Date
End If
LastDayInWeek = dtmDate - _
Weekday(dtmDate, vbUseSystemDayOfWeek) + 7
End Function

There is probably a way to modify these functions to work for the 2 week period but I am not sure how to do this.
Thanks BBryan

 

[plog]

First, I don't know why you need all the days of 2014 in a table. How are you going to use that data? As for your function, here is how I would implement it:

~Get the differences in days (http://www.techonthenet.com/access/functions/date/datediff.php) between your input date and 12/28/2013.

~Mod 14 (http://msdn.microsoft.com/en-us/library/se0w9esz.aspx) that difference to get how many days after the start of a pay period that input date is

~Use the DateAdd (http://www.techonthenet.com/access/functions/date/dateadd.php) to subtract the mod days from the input date. That will put you at the beginning of the pay period

To get the last day, do the above steps, then add 13 days to the starting date.

 

[BBBryan]

Hi Plog,
Thanks for your help but I am a not clear on this.
I have.
DateDiff **** DiffDays: DateDiff("d",[InputDate],#2013-12-28#) should I be putting the #2013-12-28# in here.
Mod **** Mod: 14 Mod [DiffDays]
DateAdd **** DateAddDays: DateAdd("d",[Mod],[FKMDYDate])

I don't think I have this right.

BBryan

 

[plog]

All of those should be pretty easy to test. What do you get when you plug in test values? Are they same results you get when you manually do the calculations?

Are you trying to do that in a query, or have you written a function and those are just code snippets? This should be in a function.

 

[BBBryan]

I was trying to use this in a query.
I would like to make a function and call it from my query.

Manually if I was to do this
I would take My Dates for the whole year starting on Dec 28 2013 and make groups of 2 week payperiods. I then would know what first date and last day of this payperiod would be.
As for doing this in a function I am not sure how to get the first and last day of that 2 week period.
I thought it would be easy to modify the that 1 week function


BBryan

 

[jdraw]

Here's some code to get the WeekdayName and date for every 2 week beginning 28 Dec 2013.

Sub twoWeeks()
'to show the day and dates for each 2 week period starting 28 Dec 2013
    Dim startdate As Date
    startdate = #12/28/2013#
    Dim workdate As Date
    workdate = startdate

    Do While workdate < #2/2/2015#  'end loop
        Debug.Print WeekdayName(Weekday(workdate)) & "  " & workdate
        workdate = DateAdd("d", workdate, 14) 'every 14 days
    Loop
End Sub

RESULTS
=======

Saturday  28/12/2013
Saturday  11/01/2014
Saturday  25/01/2014
Saturday  08/02/2014
Saturday  22/02/2014
Saturday  08/03/2014
Saturday  22/03/2014
Saturday  05/04/2014
Saturday  19/04/2014
Saturday  03/05/2014
Saturday  17/05/2014
Saturday  31/05/2014
Saturday  14/06/2014
Saturday  28/06/2014
Saturday  12/07/2014
Saturday  26/07/2014
Saturday  09/08/2014
Saturday  23/08/2014
Saturday  06/09/2014
Saturday  20/09/2014
Saturday  04/10/2014
Saturday  18/10/2014
Saturday  01/11/2014
Saturday  15/11/2014
Saturday  29/11/2014
Saturday  13/12/2014
Saturday  27/12/2014
Saturday  10/01/2015
Saturday  24/01/2015

Perhaps you can use this to get what you need.

 

[gemma-the-husky]

I would do this based on week numbers.

If you want the 2 week periods starting on saturday for week 20 say, then find the saturday in week 20, and add 13 to the date for the period end.

You could use the weekday function easily enough. For week 20 something like this. I imagine there is a more elegant solution, but this will be very quick.

Testdate = dateserial(2014,1,1)+21*7  ' go past the target week to start
While weekday(testdate)<>vbsaturday and format(testdate,"ww")<>20
    testdate=testdate-1
Wend

 

[BBBryan]

Thanks JDraw
Is there a way to call this from a query
I see it can be used from a button.


BBryan

 

[BBBryan]

Hi Gemma the husky.
Thanks also.
Still a little stuck
Is that a function I would put that into
I am not sure how to put this into a query


BBryan

 

[namliam]

Int((YourDate - #28-dec-2013#)/14) will calculate which 2 week period you are in.

StartOfPeriod: YourDate + Int((YourDate - #28-dec-2013#)/14) * 14
EndOfPeriod: YourDate + (Int((YourDate - #28-dec-2013#)/14)+1) * 14 -1

You should be able to use these functions directly in a query.

NOTE above is complete aircode, may need some tweaking
NOTE 2 with a little thinking, probably 28 dec is the start of this year (as in the first day of the first week) you can automate this for every year...

 

[BBBryan]

Thanks ALL for your help I couldn't do this on my own.

What I ended up doing was in a Query.
2WeekNumber: Int(([MyDate]-#2013-12-28#)/14)
FirstDayIn2Weeks: DateAdd("d",[2WeekNumber]*14,"28/12/2013")
LastDayIn2Weeks: DateAdd("d",[2WeekNumber]*14+13,"28/12/2013")

I would have liked this in a function where I could put the Start Date of the 2 week period but don't know how to write the code for that.

Thanks BBryan

 

[namliam]

Why use a slower function if you can use an inline faster calculation?

 

[BBBryan]

I may be wrong but I think it is easier because I sometimes have different scenarios of the start date.


BBryan

 

[namliam]

And what would be your start date?

Plus just FYI, please write your dates: #12/28/2013#, to prevent any conversion problems.

If your start date is this 28 dec 2013, this can be calculated on the fly with some thinking

 

[BBBryan]

I don't know the dates it is for future. I have this for payperiods on different jobs. Some have the week ending on a Fri, sat or sun. I am using this Query/function in a lookup table so I figure out the Payperiod dates. With a query I have to manual go in and change the start date. That is why I thought a function was better. It is already built I just have to put in the start date.

Thanks BBryan

 

[jdraw]

Post the function you have.
Sounds like you need or may need a table with these fields:

JobNumber
PayOnDay Fri, Sat or Sun
StartDate

since the PayDate is related to Job. Given a JobNumber you can determine the PayOnDay and StartDate

 

[BBBryan]

I don't have a function because I don't know how to make it. All I have with the help from this site is that query I just posted.
I wanted a function instead of a query.
I have that function I use for a 1 week period (In the first posting) but don't have one for a 2 week period. I thought there was a easy way to modify that one into a 2 week one but I don't know how. And maybe it can't be done.


Thanks BBryan

 

[jdraw]

You have different Jobs that have different startDates and different PayDays --- WHAT info do you need to calculate the PayDay for a Job?

 

[BBBryan]

I need to calculate the 2 week payperiod start and end date for each Day in my lookup table. And I would know when the job starts the first day of that payperiod which could be a fri,sat or sun.

So if I started on Wed Oct-01-2014, the 2 week period would be start on Sat Sept 27 2014.
For that job 1st payperiod would start Sat Sept-27-2014 to Fri Oct-10-2014. (calculated For each day Oct 1 till the 10th)
2nd pay peroiod would be Start Sat Oct-11-2014 to Fri Oct-24-2014.(calculated For each day Oct 11 till the 24th) and so on for that whole job.

The next job might be start on Mon Dec 8 2014. And the payperiod would be Start Sun Dec-7 -2014 to Sat Dec-15-2014. and so on.


Thanks Bryyan

 

[jdraw]

Here are a couple of functions.

The functions are based on your comments that
-pay periods are 2 weeks duration
-pay period relates to a JobId
-pay periods may start on Fri, Sat or Sun

I'm working with a table tblJobPay which records:

jobPayID autonumber PK
JobId --
StartDate -- start of job pay periods
LastPayDate --most recent end of Pay period

This table requires that all Jobs have this info recorded.

Function FirstDayOfPayPeriod(iJobId As Integer) As Date
    If IsMissing(iJobId) Then Exit Function
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim i As Integer
    Set db = CurrentDb
    Set rs = db.OpenRecordset("select startdate, lastpaydate from tblJobPay where jobid =" & iJobId)
    FirstDayOfPayPeriod = DateAdd("d", 1, rs!LastPayDate)
    Set rs = Nothing
    Set db = Nothing
End Function

Function LastDayOfPayPeriod(iJobId As Integer) As Date
    If IsMissing(iJobId) Then Exit Function
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim i As Integer
    Set db = CurrentDb
    Set rs = db.OpenRecordset("select startdate, lastpaydate from tblJobPay where jobid =" & iJobId)
    LastDayOfPayPeriod = DateAdd("d", 14, rs!LastPayDate)
    Set rs = Nothing
    Set db = Nothing
End Function

You update the LastPayDate when a PayPeriod has been processed.
Here's a sample routine and a test

Sub UpdateLastPayPeriod(ijobid As Integer)
    Dim db As DAO.Database
    Dim ssql As String
    On Error GoTo UpdateLastPayPeriod_Error

    ssql = "Update tblJobPay SET LastPayDate = DateAdd('d', 14, LastPayDate) where jobid = " & ijobid
    Set db = CurrentDb
    db.Execute ssql, dbFailOnError

    On Error GoTo 0
    Exit Sub

UpdateLastPayPeriod_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure UpdateLastPayPeriod of Module AWF_Related"
End Sub

Sub testOC()
Dim ijobid As Integer
ijobid = 1
UpdateLastPayPeriod (1)
End Sub

This is meant to show you one possible way to accomplish what you are trying to do. You should not change your production system until you understand the code and the proper usage and test it thoroughly.