Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old December 3rd, 2004, 11:08 AM
Registered User
 
Join Date: Dec 2004
Location: Fremont, CA, USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Calculating number of days between multiple dates

Can any one help me with creating codes that will calculate how many days an employee is getting pay per [u]fiscal year</u> and at what level? My fiscal year starts on July 1st and ends on June 30th the following year.

For example Persno 11527 would get pay for:

fiscal year 2005 (7/1/2004 to 6/30/2005) at
Level 1 from 11/8/2004 to 5/1/2005 plus
Level 2 from 5/2/2005 to 6/30/2005


fiscal year 2006 (7/1/2005 to 6/30/2006) at
Level 2 from 7/1/2005 to 04/26/2006 plus
Level 3 from 4/27/2006 to 6/30/2006


fiscal year 2007 (7/1/2006 to 6/30/2007) at
Level 3 from 7/1/2006 to 4/21/2007 plus
Level 4 from 4/22/2007 to 6/30/2007


fiscal year 2008 (7/1/2007 to 6/30/2008) at
Level 4 from 7/1/2007 to 4/15/2008 plus
Level 5 from 7/16/2008 to 6/30/2008


For records with no enddate the enddate is assumed to be equalled to 12/31/9999.


Here's a sample of my records. Persno, PSGroup, Level, Daily Rate, StartDate, and EndDate.


Persno PSGroup Level Daily Rate StartDate EndDate
11527 B420 1 $138.44 11/8/2004 05/01/2005
11527 B420 2 $145.12 5/2/2005 04/26/2006
11527 B420 3 $152.06 4/27/2006 04/21/2007
11527 B420 4 $159.44 4/22/2007 04/15/2008
11527 B420 5 $167.16 4/16/2008
11518 B466 1 $172.00 10/4/2004 04/01/2005
11518 B466 2 $180.25 4/2/2005 03/27/2006
11518 B466 3 $188.95 3/28/2006 03/22/2007
11518 B466 4 $198.17 3/23/2007 03/16/2008
11518 B466 5 $207.92 3/17/2008
11503 B564 1 $273.98 8/23/2004 02/01/2005
11503 B564 2 $287.42 2/2/2005 01/27/2006
11503 B564 3 $301.65 1/28/2006 01/22/2007
11503 B564 4 $316.50 1/23/2007 01/17/2008
11503 B564 5 $332.13 1/18/2008
11489 R34 $513.46 8/9/2004
11483 R40 $673.08 8/2/2004
11481 R32 $446.15 7/26/2004
11470 R29 $384.42 7/19/2004
11454 U75 1 $275.04 11/1/2004 07/01/2005
11454 U75 2 $293.84 7/2/2005 06/26/2006
11453 B543 1 $247.89 7/6/2004 01/01/2005
11453 B543 2 $260.01 1/2/2005 12/27/2005
11453 B543 3 $272.66 12/28/2005 12/22/2006
11453 B543 4 $286.10 12/23/2006 12/17/2007
11453 B543 5 $300.16 12/18/2007
11451 B543 1 $247.89 6/21/2004 12/01/2004
11451 B543 2 $260.01 12/2/2004 11/26/2005
11451 B543 3 $272.66 11/27/2005 11/21/2006
11451 B543 4 $286.10 11/22/2006 11/16/2007
11451 B543 5 $300.16 11/17/2007
11436 R32 $461.54 6/7/2004
11398 B517 2 $229.80 9/27/2004 09/01/2005
11398 B517 3 $240.95 9/2/2005 08/27/2006
11398 B517 4 $252.72 8/28/2006 08/22/2007
11397 B517 2 $229.80 9/27/2004 09/01/2005
11397 B517 3 $240.95 9/2/2005 08/27/2006
11397 B517 4 $252.72 8/28/2006 08/22/2007
11396 B517 2 $229.80 9/27/2004 09/01/2005
11396 B517 3 $240.95 9/2/2005 08/27/2006
11396 B517 4 $252.72 8/28/2006 08/22/2007


Thank you,


Vann
Reply With Quote
  #2 (permalink)  
Old December 3rd, 2004, 01:02 PM
ea ea is offline
Registered User
 
Join Date: Nov 2004
Location: New York, NY, USA.
Posts: 8
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Here's the way I would do it.

First I would create a query with the following calculated field:
1) Showing the fiscal year for the StartDate i.e. FisYear: IIf(Month(StartDate)< 7, Year(StartDate), Year(StartDate) + 1)

2)A field that shows the number of days from start date to end date i.e. DaysOfWork: DateDiff("D", EndDate, StartDate). Check the DateDiff function to make sure I gave you the right syntax.

3) Then I would check if the date range crossed fiscal year borders and do math accordingly: FisYr1:IIf(StartDate < DateSerial(FisYear, 7, 1) And EndDate >= DateSerial(FisYear, 7, 1), DateDiff("d", DateSerial(FisYear, 7, 1), Startdate), DaysOfWork)

4) Finally if DaysOfWork is greater than FisYr1 then get the difference and put it into FisYr2. Otherwise FisYr2 is 0. FisYr2: IIf(DaysOfWork > FisYr1, DaysOfWork - FisYr1, 0)

5) I forgot to include that you'll need to know the Year for FisYear2: FisYear: IIf(Month(StartDate)< 7, Year(StartDate + 1), Year(StartDate) + 2)


That will Break should break the year up for you.

After that I think you're going to have to write some VBA code to insert FisYr1 and FisYr2 into two different records.

Once that's done you should be able to run a totals query to aggregate the rest.

This is off the top, so there might be some logic or syntax errors but it's worth a shot.

Let me know how it goes.
ea


Reply With Quote
  #3 (permalink)  
Old December 3rd, 2004, 01:29 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Norwich, Norfolk, United Kingdom.
Posts: 129
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Can a Level span more than two fiscal years?

Clive Astley
Reply With Quote
  #4 (permalink)  
Old December 3rd, 2004, 07:20 PM
Registered User
 
Join Date: Dec 2004
Location: Fremont, CA, USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by ea
 Here's the way I would do it.

First I would create a query with the following calculated field:
1) Showing the fiscal year for the StartDate i.e. FisYear: IIf(Month(StartDate)< 7, Year(StartDate), Year(StartDate) + 1)

2)A field that shows the number of days from start date to end date i.e. DaysOfWork: DateDiff("D", EndDate, StartDate). Check the DateDiff function to make sure I gave you the right syntax.

3) Then I would check if the date range crossed fiscal year borders and do math accordingly: FisYr1:IIf(StartDate < DateSerial(FisYear, 7, 1) And EndDate >= DateSerial(FisYear, 7, 1), DateDiff("d", DateSerial(FisYear, 7, 1), Startdate), DaysOfWork)

4) Finally if DaysOfWork is greater than FisYr1 then get the difference and put it into FisYr2. Otherwise FisYr2 is 0. FisYr2: IIf(DaysOfWork > FisYr1, DaysOfWork - FisYr1, 0)

5) I forgot to include that you'll need to know the Year for FisYear2: FisYear: IIf(Month(StartDate)< 7, Year(StartDate + 1), Year(StartDate) + 2)


That will Break should break the year up for you.

After that I think you're going to have to write some VBA code to insert FisYr1 and FisYr2 into two different records.

Once that's done you should be able to run a totals query to aggregate the rest.

This is off the top, so there might be some logic or syntax errors but it's worth a shot.

Let me know how it goes.
ea


Hi EA,

Thank you. I'll work on it this weekend & I'll let you know how it works out. Again, thanks!

Vann
Reply With Quote
  #5 (permalink)  
Old December 3rd, 2004, 07:26 PM
Registered User
 
Join Date: Dec 2004
Location: Fremont, CA, USA.
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by Clive Astley
 Can a Level span more than two fiscal years?

Clive Astley
Hi Clive,

Yes, once an employee reaches the maximum level, either at 3, 5 or 7 then he/she will stay at that level for the duration of his/her employment.

Before an employee reaches the maximum level, his/her pay will cross fiscal year due to the time it takes to move from one level to the next either six months or one year.

Thank you,


Vann
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Need to query dates after 90 days old melkin SQL Language 9 October 30th, 2007 08:03 AM
How to calculate a number of days between 2 date windy417 Pro JSP 1 July 20th, 2006 07:09 PM
Calculating numbers of days similar to Excel klott16 Access 7 January 3rd, 2006 01:43 PM
Calculating hours and minutes between to dates timmaher Access VBA 4 May 10th, 2005 07:36 AM
How to calculate the work days beetwen two dates. andres_pm Javascript 1 October 7th, 2004 06:23 AM



All times are GMT -4. The time now is 05:22 AM.


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.