Consecutive Date Ranges

[ProwlingCamel]

Hey guys,


I have reports that pull from queries to display information on employees and their hours spent offsite. The queries that are being used are setup as follows:

QsspWeekly - Provides information only on last work week (sun-sat)
QsspMonthly - Provides information (month to date)
QsspYear - Provides information (year to date)

One of the reports that I generate, is to display the employee, and the dates that that employee worked offsite. The requester of the report wants to only see the dates as ranges.

Example: John Doe worked offsite on 9/1,9/2,9/3,9/5 and 9/6.
I would like to display it as:
John Doe 9/1/15-9/3/15
9/5/15-9/6/15

My first attempt was to use Min and Max dates in 2 columns, which kind of works for the weekly report, but only by luck. Since it is only pulling the highest and lowest dates, the above example would yield 9/1/15-9/6/15 as the result.

I realize that there is a datediff function...and I realize that for a consecutive date range, the date diff would be less than or equal to 1 (less than incase they had 2 shifts in a single day). I feel like I have all of the pieces there, but for the life of me, I can't get this working, and I am extremely frustrated at the simplicity that I am most likely overlooking. If somebody could explain to me (in the most dummy proof way possible) how to accomplish my goal, they would be my hero for the day! Lol.

Thanks in advance for any help!

[Homegrownandy]

how are you gathering this data in the first place? do they exist as separate dates assigned to 'john'?

[ProwlingCamel]

Homegrownnandy,
Yes. I have a form where i input the Employee, Hours, and Date. So there are multiple records for "John". Example. John Doe 9 9/1/15, John Doe 8 9/2/15 etc.

[Homegrownandy]

and you want to show all results in between the two date ranges?

>=[Forms]![SchedulerMenuF]![From_Date_TXT] And <=[Forms]![SchedulerMenuF]![To_Date_TXT]

in your query apply that code. this is copied from something I did so you need to change the names. its taking two date values and showing the dates that fall between them.

If this isn't the answer you need or you need more help let us know.

[ProwlingCamel]

I don't think this is what I needed, or even know what to replace in the code. What I need is to not have any individual dates show up in the report. Every date should be displayed as a range of consecutive dates. Such as John worked 9/1/15-9/10/15 and also worked 9/12/15-9/14/15. If there was a date with no consecutive dates worked, I would still like it displayed as a range. (John 9/1/15-9/1/15). I need this to work for each individual employee and not just a date parameter for the report (the appropriate dates are already calculated by the queries that they pull from.)

[ItsMe]

I am thinking you are going to need some complex code to get this to work. I don't know your data structure, but I am imagining iterating records for a data not worked and using that date as a bookmark of sorts.

[Homegrownandy]

sorry if you feel like you've explained this already,

John worked 9/1/15-9/10/15

how is this stored? are both dates on one record?