Solved

working with date function in access

Posted on 2013-01-29
6
203 Views
Last Modified: 2013-01-29
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
Comment
Question by:sptech
  • 3
  • 2
6 Comments
 
LVL 22

Accepted Solution

by:
Kelvin Sparks earned 250 total points
ID: 38830399
To add a year use DateAdd('y",1,yourstartdate).

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 49

Assisted Solution

by:Gustav Brock
Gustav Brock earned 250 total points
ID: 38830439
FutureDate = DateAdd("yyyy", 1, Date)

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

/gustav
0
 

Author Comment

by:sptech
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 49

Expert Comment

by:Gustav Brock
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

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

Expert Comment

by:Gustav Brock
ID: 38831095
OK.

/gustav
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question