Dates from and too query

[TrevorM]

Hi All,

I have a question and Its a bit of a hard one,

I have made a vehicle database which lists when equipment goes on breakdown/down time with a from and two fields,

I have a query currently in place using "between (FromDate) and (ToDate)" under the From field only to show anything that went "on" breakdown between these dates and is working well but there is a greater issue


For example I have
Car 1 on breakdown from 01-Sept-13 to 30-Nov-13
and
Car 2 on breakdown from 01-Oct-13 to 30-Oct-13.

If I search from 15-Sept-13 to 15-Nov-13 I will find Car 2 as its between the dates, but Car 1 is not showing it, by right is still on breakdown on the dates of my query search as it went on and off before and after the defined dates.

Is there a way i can include items like these events?

Hope this makes sense.

[pbaldy]

Does this help?

http://www.baldyweb.com/OverLap.htm

[TrevorM]

Yeah deffintily does thats exactly what Im looking for, thanks heap, will try to put it into action and see how i go,

Many many thanks been self teaching everything I know in the last 1-2 weeks and this is what stumped me, thanks again.
-Trev

[pbaldy]

No problem, and welcome to the site by the way!

[TrevorM]

No worries thanks for the welcome,

With the "SQL" info that's the Query isn't it as I have learned it means "Structured Query Language"

I load the search code/criteria in to the design view of my query under the Criteria section or do i run this with in a Macro/Module?

Currtly my "Breakdown From" has the following Criteria (Between [Forms]![Menu - BreakDown Report]![BrkDwnFrom] And [Forms]![Menu - BreakDown Report]![BrkDwnTo]) as the dates it uses to search is collected from a form as they will never be the same month to month.

Thanks

[pbaldy]

It can just be a query. The key is that rather than using Between on one field you're "testing the given start date against the end date in the table, and the given end date against the start date in the table."