Solved

Access Date Range bleeding into another year.

Posted on 2015-01-23
19
72 Views
Last Modified: 2015-01-24
In Access, I have a ArriveDate and LeaveDate, I need to count the dates in the current year.  When the ArriveDate is 12/29/15 and the LeaveDate is 1/3/16, I only want it to count the 3 days.  I am currently using DateDiff("d",[ArriveDate],[LeaveDate]).. but this doesn't work when the LeaveDate goes into the next year... Additionally, when it is the next year, I need to begin to count the days of the current year.. So if the year is 2016 and the ArriveDate is 12/29/15 and the LeaveDate is 1/3/16- it should count 2 days.
0
Comment
Question by:Shaft960
  • 12
  • 7
19 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40567459
IIf(Year([arrivedate])=Year([leavedate]),DateDiff("d",[ArriveDate],[LeaveDate]),DateDiff("d",DateValue(Year(Now()) & "/1/1"),[LeaveDate]))
0
 

Author Comment

by:Shaft960
ID: 40567501
Simon,

Thanks for the comment, I keep getting an error message of wrong number of arguments.
0
 
LVL 18

Expert Comment

by:Simon
ID: 40567510
Sorry, I amended it after you tested. Please try again.

I'm not sure what range of dates you're working with. If date ranges are totally inside previous years, what days count would you want?
0
 

Author Comment

by:Shaft960
ID: 40567528
Simon,

There is no error wrong number of arguments error, But one part of the equation is not working right.  

If the year is 2015 and I put in an ArrivalDate of 12/29/14 and a LeaveDate of 1/3/15... it gives me the correct number of days as 2, but if it is still 2015, if I put in ArrivalDate of 12/29/15 and a LeaveDate of 1/3/16... it gives me 367 days... Where it should be the 29, 30 and 31st for 3 days.
0
 
LVL 18

Expert Comment

by:Simon
ID: 40567538
OK. All durations that cross year boundaries are counted from 1st January of the year of the leaving date (rather than the year of the current date):
 IIf(Year([arrivedate])=Year([leavedate]),DateDiff("d",[ArriveDate],[LeaveDate]),DateDiff("d",DateValue(Year([LeaveDate]) & "/1/1"),[LeaveDate]))

Open in new window

0
 

Author Comment

by:Shaft960
ID: 40567542
Simon,

I think we almost have it, but it is still a bit incorrect.. I put in this

Arrive                    Leave       Total
12/27/2015      1/4/2016            3

It should show 5 since it is 2015.  If it was 2016 then it would be correct at 3.  It should have counted 12/27 thru 12/31 for 5 days..

By the way, you are very good.
0
 

Author Comment

by:Shaft960
ID: 40567547
Also, when I change the year to 2016, if there is data that has dates of 12/15/15 to 12/20/15 it should show 0 since it is 2016... it is showing 5 right now
0
 

Author Comment

by:Shaft960
ID: 40567549
Basically, we are tracking how many days in a calendar year that a person camps in a campsite.  The can only camp in the park 28 times a year.  So we enter the arrival date and leave dates and it calculates.. it was working fine except when the year changes and it bleeds over
0
 
LVL 18

Expert Comment

by:Simon
ID: 40567578
This now gives days in the CURRENT year, but doesn't deal well with date ranges totally outside the current year. It can be expanded to deal with those if necessary. It depends how you are using it in your application. If it's on a form where you are only accepting bookings that include some days in the current year it should be OK.

DateDiff("d",IIf(Year([arrivedate])<Year(Now()),DateSerial(Year(Now()),1,1),[arrivedate]),IIf(Year([leavedate])>Year(Now()),DateSerial(Year(Now()),12,31),[leavedate]))

Open in new window

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Shaft960
ID: 40567594
Simon,

To start, I really appreciate your help.  I hate to keep giving you more issues.... but

When I put in an arrival date of 12/26/15 and a leave date of 1/2/16 - it shows 5 days.... it should show 6 days from 12/26 to 12/31.

When I put in Arrival of 1/2/16 and a leave date of 1/5/16 it should show Zero (0) because it is 2015, but it shows -2...
0
 
LVL 18

Expert Comment

by:Simon
ID: 40567627
Sorry, slow trip to understanding...

You want to count including both the beginning and end dates where it is current year.

Are you using this in a calculated form field or a query? I ask because this might be easier to do as a VBA function.

This should now cater for inclusive date-range counts in current year and zero for date ranges wholly in future years.
iif(year(arrivedate)>year(now()),0,DateDiff("d",IIf(Year([arrivedate])<Year(Now()),DateSerial(Year(Now()),1,1),[arrivedate]),IIf(Year([leavedate])>Year(Now()),DateSerial(Year(Now()),12,31),[leavedate]))+1)

Open in new window


..It won't cater for date ranges wholly in previous years - is that an issue?
0
 

Author Comment

by:Shaft960
ID: 40567654
Simon,

I am using a query.  And it was working correctly with this formula except for where it intersects a new year.

CampTotal: DateDiff("d",[Arrivedate],[Leavedate])

We are tracking how many days in a year (Jan 1 to Jan 31) that someone camps in a campground.  

The above formula works perfectly until you reach for example – Arrival Date of 12/29/15 and Leave Date of 1/3/16.  

In this example above, if it was still 2015, it should show 3 days.  If it was 2016 it should show 2 days for Jan 1 and Jan 2.  The leaving date does not count towards the total.

Thank you,

Steven
0
 
LVL 18

Accepted Solution

by:
Simon earned 500 total points
ID: 40567671
Thanks for your patience Steven. My last attempt before UK bedtime...

iif(year(arrivedate)>year(now()),0,DateDiff("d",IIf(Year([arrivedate])<Year(Now()),DateSerial(Year(Now()),1,1),[arrivedate]),IIf(Year([leavedate])>Year(Now()),DateSerial(Year(Now()+1),1,1),[leavedate])))

Open in new window


I haven't time to test it as I have to go to another PC (which has a noisy keyboard that annoys my wife), but I hope it works. I've rolled back from including both start and end dates but changed the counting for LeaveDates in the next year.
Fingers crossed...
0
 

Author Comment

by:Shaft960
ID: 40567685
Simon,

Thanks for the attempt.  It is now showing

Arrival              Leave              Total
12/29/2015      1/3/2016            -362

It was working better with some of your earlier stuff..  I haven't used Experts Exchange in a while... This is my first question in a while...

Go to bed and maybe we can figure it out tomorrow... Thank you from California, USA
0
 

Author Comment

by:Shaft960
ID: 40567758
Simon,

I attempted this on my own and it is working but I need to figure the calculations where I have the text... Also, the "Today" function wasn't working so I needed to use "Now"

CampTotal: IIf(Year([ArriveDate])=Year(Now()) And Year([LeaveDate])=Year(Now()),DateDiff("d",[ArriveDate],[LeaveDate]),IIf(Year([ArriveDate])<Year(Now()) And Year([LeaveDate])<Year(Now()),"Zero",IIf(Year([ArriveDate])<Year(Now()) And Year([LeaveDate])=Year(Now()),"Jan1toLeaveDate",IIf(Year([ArriveDate])=Year(Now()) And Year([LeaveDate])>Year(Now()),"ArriveDateto1231","DoNothing"))))
0
 

Author Comment

by:Shaft960
ID: 40567787
Simon,

I figured it out... here is the formula.... I would have never figured it out without your help.

CampTotal: IIf(Year([ArriveDate])=Year(Now()) And Year([LeaveDate])=Year(Now()),DateDiff("d",[ArriveDate],[LeaveDate]),IIf(Year([ArriveDate])<Year(Now()) And Year([LeaveDate])<Year(Now()),0,IIf(Year([ArriveDate])<Year(Now()) And Year([LeaveDate])=Year(Now()),DateDiff("d",DateSerial(Year([LeaveDate]),1,1),[LeaveDate]),IIf(Year([ArriveDate])=Year(Now()) And Year([LeaveDate])>Year(Now()),DateDiff("d",[ArriveDate],DateSerial(Year([ArriveDate]),12,31)+1),0))))
0
 

Author Closing Comment

by:Shaft960
ID: 40567790
I would have never been able to complete the formula without the help of Simon.  Thank you.
0
 
LVL 18

Expert Comment

by:Simon
ID: 40567949
Whew. I'm sorry that was such a struggle. I really should have gone to bed two hours earlier!
0
 

Author Comment

by:Shaft960
ID: 40567953
I'm still up... 0154 am in California
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now