Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# working with date function in access

Posted on 2013-01-29
Medium Priority
226 Views
my company gives comptime with strings attached.  first off comptime expires one year after it has been earned or 26 pay periods.

How do I enter a future date that is one later than the current date?

and

How do I compute pay periods that the comptime was earned in?
0
Question by:sptech
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 3
• 2

LVL 22

Accepted Solution

Kelvin Sparks earned 1000 total points
ID: 38830399

Counting fortnights is a bit harder as there's no frthight concept I know of in Access.

Best to count days and divide by 14.

Something like Int(DATEDIFF(:d",oldersate, newerdate)/14) would give you the number of completed fortnights

Kelvin
0

LVL 52

Assisted Solution

Gustav Brock earned 1000 total points
ID: 38830439

> 26 pay periods.
How long is a pay period?

/gustav
0

Author Comment

ID: 38830482
I figured out the DateAdd function so thanks to both of you for that.  catus_data it works out to be every 14.03 days.  That is why some months have 2 pay periods and  twice a year we have 3 pay periods. below is the pay period schedule for 2013:

1/10 and 1/24, 2/7 and 2/21, 3/7 and 3/21, 4/4 and 4/18, 5/2 and 5/16 and 5/30, 6/13 and 6/27, 7/11 and 7/25, 8/8 and 8/22, 9/5 and 9/19, 10/3 and 10/17 and 10/31, 11/14 and 11/27 (thanksgiving 11/28 so pay period backone day), 12/12 and 12/26.

so an employee travels to the home office on 2/7 he is in pay period 3. on returning from the home office on 3/7 he is in pay period 4.

How do I:

1) determine the pay period not just for 2013 but as far out as say 2020?
2) once that is determined, how do I determine which pay period(s) my traveler traveled in?
0

LVL 52

Expert Comment

ID: 38830527
To calculate that you will need a start date from which you add up the 14.03.

You also must be sure how to round the accumulated .03 days. Will one day be added when you pass 0.5 day after 17 periods, or would it not happen until a full day as accumulated after 34 periods? And will the remaining part (0.01 or 0.02) be carried over for the next 17 or 33/34 periods?

Further, you must have a list of public holidays which will shift the pay date one day backwards.

Having this you can just look up the travel start and end date for your travelers.

/gustav
0

Author Closing Comment

ID: 38831059
I am dropping the 26 pay period from the calculations.  My users will just have to add this manually.
0

LVL 52

Expert Comment

ID: 38831095
OK.

/gustav
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
###### Suggested Courses
Course of the Month10 days, 3 hours left to enroll