Complicated Date Question

[tyjan10]

Please Help!!!

- My Query has this information: Name, Start Date, End Date
- I created a calculation to count the DateDiff between the Start/End Date.
- Also consolidated dates in one field like this: 01/01/11-01/01/12 for Start/End Dates.

- I need for to count the DateDiff only for the days that fall between Date() -365 and Date() +365. So if the -365th day or +365th day is in the middle of a range of dates I do not want days on the other side calculated.

- Next I need the 730 day window to slide to the right by one day everyday, so basically to compute the days - or + 365 as of the current date.

Last I need the total days difference for each person -730. So for example if a person had 3 events I will know how many days they were gone during a 730 day period using a sliding scale of -/+ 365 days.

Please help!!! I have racked my brain and I can't figure this out. Thanks in advance....

 

[khodr]

I think if you explain more about your query or if you attach some images here it will be easier, one more thing you must plan it logically because if you are doing it without a proper math calculation it won't work, anyway I couldn't really figure out how you want to calculate it attach an image about your query design and query output maybe by then it can be easier

 

[plog]

Yes, sample data will help. Provide examples of data in your table and then what you want this query to produce based on that sample data. This is the preferred format:

tblName
FirstFieldName, SecondFieldName, ThirdFieldName
1/1/2012, 3/31/2012, "Bob"
2/28/2012, 5/1/2012, "Larry"

 

[JHB]

- Next I need the 730 day window to slide to the right by one day everyday, so basically to compute the days - or + 365 as of the current date.

You can max have 256 fields (columns) in a query/table
For me it sounds like you want a Pivot query/table with min. 730 fields (columns).

 

[tyjan10]

OK. My Table looks like thisERSTEMPO EventsIndividual Event Soldier Name Event Start Date End Date Remarks20 Jones, Tim A. Vacation 20-Nov-11 25-Dec-11 21 Jones, Tim A. Trip 12-Jun-12 01-Jun-12 22 Jones, Tim A. Proj Trip: 10-Dec-13 15-Jan-14 **My Form only has this information: Unit, Company, Rank, and Individual Name My Sub Form Looks like thisERSTEMPO Events subform1Soldier Name Event Start Date End Date RemarksJones, Tim A. Vacation 20-Nov-11 25-Dec-11 Jones, Tim A. Trip 12-Jun-12 01-Jun-12 Jones, Tim A. Proj Trip: 10-Dec-13 15-Jan-14 My Query Currently looks like thisates ConsolidatedSoldier Name PERSTEMPO Events Dates Start Date End Date Expr1Jones, Tim A. 11/20/2011-12/25/2011 20-Nov-11 25-Dec-11 35Jones, Tim A. 6/12/2012-6/1/2012 12-Jun-12 01-Jun-12 -11Jones, Tim A. 12/10/2013-1/15/2014 10-Dec-13 15-Jan-14 36• I had the query put both the Start and End Date together using this function: Dates: [Start Date] & "-" & [End Date]• I used this expression to calculate the date Difference between the Start and End date: Expr1: DateDiff("d",[Start Date],[End Date])The information I need is this:• I need the Expr1 days (the difference between the [Start Date]and [End Date]) to change based on the current Date. • I need it to compute Expr1 by looking one year back (- 365 days) and one year forward (+ 365 days) For any [Start Date]and [End Date]that fall inside the + or – 365 range (730 days total) need to be counted. • The tricky part is when a [Start Date]and [End Date] falls like this: 1. If the -365 day is (10 DEC 12) but the event dates are: [Start Date] =5 Dec 12, [End Date] = 15 Dec 12. I would only want to count half of those days because the remaining dates are out of the range I need.2. If the +365 day is (10 DEC 12) but the event dates are: [Start Date] =5 Dec 14, [End Date] = 15 Dec 14. I would only want to count half of those days because the remaining dates are out of the range I need.• I need the calculations to change based on the current Date that I run the query.I hope that I have explained my situation more. Thanks for any assistance….

 

[JHB]

Is that what you want to get, see the attached picture, (it shows a table with sample data, and the result from the query, current date + - 20days)?
The query is showed below:

SELECT MyTable.Name, MyTable.Event, Date()-20 AS [Day-20], Date()+20 AS [Day+20], MyTable.StartDate, MyTable.EndDate, IIf([StartDate]<Date()-20,IIf([EndDate]>Date()+20,DateDiff("d",Date()-20,Date()+20),DateDiff("d",Date()-20,[Enddate])),IIf([enddate]>Date()+20,DateDiff("d",[Startdate],Date()+20),DateDiff("d",[Startdate],[Enddate]))) AS Countdays
FROM MyTable
WHERE (((MyTable.StartDate) Between Date()-20 And Date()+20)) OR (((MyTable.StartDate)<Date()-20) AND ((MyTable.EndDate)>=Date()+20)) OR (((MyTable.EndDate) Between Date()-20 And Date()+20));