A Date issue

[MichHart]

Hi guys, i was hoping you could help me with another "challenge" i have in my db.

I have a table detailing events. Each event has a start and end date field.

Id love to be able to generate a query for a report, which again is controlled by a start and end date, which would break down every day for the search dates showing the events....

Hard to describe really, something like this....

From my event table -

Start______End_______Event
01/01/2011 05/01/2011 I am an Event



if if my query is between 01/01/2011 and 07/01/2011 the result would show like this -

Date______Event
01/01/2011 I am an Event
02/01/2011 I am an Event
03/01/2011 I am an Event
04/01/2011 I am an Event
05/01/2011 I am an Event
06/01/2011 -
07/01/2011 -

Is this possible to do in access? I know i can do this in excel.

 

[xenou]

Assuming you know how to write a select query with criteria BETWEEN (Date1 and Date2) or (>= Date1 AND < (Date2 + 1)), you can easily write a query that uses parameters based on user input. But you haven't actually defined how an event qualifies. It seems you have a choice of:

• starting before the date range, and ending within the date range (partial overlap "rightwards")
• starting and ending within the date range (complete overlap)
• starting within the date range but not ending in the date range (partial overlap "leftwards")

Or, of course, counting any of these as a "hit".