• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 82
  • Last Modified:

Access Date Range bleeding into another year.

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
Shaft960
Asked:
Shaft960
  • 12
  • 7
1 Solution
 
SimonCommented:
IIf(Year([arrivedate])=Year([leavedate]),DateDiff("d",[ArriveDate],[LeaveDate]),DateDiff("d",DateValue(Year(Now()) & "/1/1"),[LeaveDate]))
0
 
Shaft960Author Commented:
Simon,

Thanks for the comment, I keep getting an error message of wrong number of arguments.
0
 
SimonCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Shaft960Author Commented:
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
 
SimonCommented:
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
 
Shaft960Author Commented:
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
 
Shaft960Author Commented:
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
 
Shaft960Author Commented:
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
 
SimonCommented:
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
 
Shaft960Author Commented:
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
 
SimonCommented:
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
 
Shaft960Author Commented:
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
 
SimonCommented:
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
 
Shaft960Author Commented:
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
 
Shaft960Author Commented:
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
 
Shaft960Author Commented:
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
 
Shaft960Author Commented:
I would have never been able to complete the formula without the help of Simon.  Thank you.
0
 
SimonCommented:
Whew. I'm sorry that was such a struggle. I really should have gone to bed two hours earlier!
0
 
Shaft960Author Commented:
I'm still up... 0154 am in California
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 12
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now