Solved

Access Date Range bleeding into another year.

Posted on 2015-01-23
19
66 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:SimonAdept
Comment Utility
IIf(Year([arrivedate])=Year([leavedate]),DateDiff("d",[ArriveDate],[LeaveDate]),DateDiff("d",DateValue(Year(Now()) & "/1/1"),[LeaveDate]))
0
 

Author Comment

by:Shaft960
Comment Utility
Simon,

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

Expert Comment

by:SimonAdept
Comment Utility
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
Comment Utility
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:SimonAdept
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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:SimonAdept
Comment Utility
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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:Shaft960
Comment Utility
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:SimonAdept
Comment Utility
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
Comment Utility
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:
SimonAdept earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I would have never been able to complete the formula without the help of Simon.  Thank you.
0
 
LVL 18

Expert Comment

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

Author Comment

by:Shaft960
Comment Utility
I'm still up... 0154 am in California
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

744 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

10 Experts available now in Live!

Get 1:1 Help Now