Dear Spence:
John's solution is a good way to get the job done no matter how
perverse the definition is for when each biweekly period starts. I
would change only one thing: I would store ONLY the StartDates, and
no EndDates. I would do a little extra query work so that you could
not have "gaps" due to errors in data entry. Each EndDate should be
the StartDate + 13 days, so it is redundant to enter both (my opinion)
although it does make for a simpler query.
Next, it occurs to me it should be enough to just enter the date on
which the first biweekly period begins in each year. There would be
26, or occasionally 27 biweekly periods in each year. If you
calculate the number of days between any given date and the date on
which the first period of the year began, then do integer division by
14, you will get a number between 0 and 51 (or 52 in years with 53
weeks) to which that date maps. Add one if you prefer 1 to 52 (or 53)
instead of 0 to 51 (or 52).
The problem I'm trying to aviod is one caused by having to enter over
100 dates for evey year you are setting up. If any of these are set
up incorrectly, you're suddenly going to see inexplicable errors in
the application that may be difficult to trace. Reducing this data
entry to 1 date per year should be an advantage when viewed from that
perspective.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
I have a query that had fields [StartDate] and [EndDate].
Is there a way to create a field that converts the data to
biweekly pay periods?
So, if I have something like:
[StartDate]=12/01/03
[EndDate]=12/10/03
I need a feild that will convert the data to something
like [DecemberFirstHalf].
I'd suggest creating a 26-row table - or 26 rows times the number of
years that you want the operation to work - containing three fields:
PeriodStart, PeriodEnd, and PeriodName. Note that biweekly periods can
and often will span two months, so "DecemberFirstHalf" may not in fact
be valid!
You can join this table to your existing tables, perhaps using a "non
equi join" if the dates don't match exactly. Note that your example of
start and end dates do not correspond to a two-week period (the
EndDate should be 12/12/03).
