coding 2 week periods

[snipe]

Hey guys,



For the last, um, forever, my company has been paying bonuses on a monthly basis. I have a program that, upon open, determines the date range to run a report. That report is then sent out to the respective branch managers, and the program closes itself. If the day is the first of the month, it runs the report for the entire last month. Otherwise it picks the to and from dates to be the first day of the month and yesterday, respectively. Here is the code that accomplishes that:

'Sets the to and from dates
If DatePart("d", Date) = 1 Then
    datefrom = DateSerial(DatePart("yyyy", Date), DatePart("m", Date) - 1, 1)
    dateto = DateSerial(DatePart("yyyy", Date), DatePart("m", Date), 0)


    Else
    datefrom = DateSerial(DatePart("yyyy", Date), DatePart("m", Date), 1)
    dateto = DateSerial(DatePart("yyyy", Date), DatePart("m", Date), DatePart("d", Date) - 1)


End If


Forms![Montage Sales_gers]!fromdate = datefrom
Forms![Montage Sales_gers]!todate = dateto

Starting yesterday, the powers that be have decided to pay the bonuses out on a biweekly schedule. The periods will be a Monday thru the next Sunday, i.e. 1/5/15 - 1/18/15.

I cannot figure out how to do this. Any thoughts?

If you can even put me on the right track that would be perfect.

Thanks guys!

[June7]

Sounds like grounds for justifiable homicide - shoot the powers!

So basically the odd number weeks can become the identifier for bi-weekly period. I did a quick experiment in query:

SELECT DateField, DatePart("ww",[DateField]) AS Wk, [Wk]-IIf([Wk] Mod 2=0,1,0) AS BiWk FROM Table1;

[snipe]

Ah definitely on to something with the week number.

Messing around with the first day of the week, and first week of the year. You are right in having the odd numbers be the final week of the period. Doing this all in VBA, hopefully.

Going to keep on playing around with this... I have until the 15th to get it done

[June7]

I did not use VBA, just calc in query.

[snipe]

I saw. I actually prefer to do this kind of stuff in VBA, usually. You're calc up there definitely pointed me in the right direction! Thanks for that!

[June7]

Don't forget - will also need the year for selecting/aggregating records.

[snipe]

June7,

This is what I have come up with for the dates. There will be DateFrom and DateTo. DateTo will always be yesterday. DateFrom is using the following code:

Public Function DateFrom(Today As Date) As Date


'if Today is the first day of the pay period, then pull the entire last period
If DatePart("ww", Today, vbMonday, vbFirstFullWeek) Mod 2 = 1 And _
    DatePart("w", Today, vbMonday, vbFirstFullWeek) = 1 Then
    DateFrom = Today - 14


Else


' determine if current day is in 1st or 2nd week of pay period
' 1st weeks are odd, 2nd are even
Dim DayMod As Integer
If DatePart("ww", Today, vbMonday, vbFirstFullWeek) Mod 2 = 0 Then
    DayMod = 7
    Else
    DayMod = 0
End If


'determine how many days back you need to go to get to monday
'of the first week in the pay period
DayMod = DayMod + (DatePart("w", Today, vbMonday, vbFirstFullWeek) - 1)


DateFrom = DateAdd("d", -1 * DayMod, Today)


End If


End Function

Do you see any issues? Anything that could be done better or more efficiently? Testing the immediate window worked great.