parameter query for date range

[Zuzu]

Hello all

I am trying to write a parameter query that shows me what equipment is booked during a specific time range.

For example, the booking table is setup with the EquipmentID, FROMDATE and THRUDATE.

EQIUPMENT..........FROMDATE......THRUDATE
Equipment A..........1/2/17..........28/2/17
Equipment B..........5/2/17..........10/2/17
Equipment C..........18/2/17.........20/2/17

How do i write the query to show me all Equipment that is booked out from the 5th - 15th of Feb?

The query should return 2 bookings using the above data (Equipment A and Equipment B)..... however i can only get the query to return Equipment B, as that one has a start date of the 5th. I cannot get the query to also add the Equipment A booking, which is still active for that query date range.

Any help greatly appreciated.

 

[HiTechCoach]

It is always helpful to see the SQL that you are using so we can help spot the issue.

Please post your query's SQL.

Using your example the WHERE criteria would be something like:

... WHERE ([FROMDATE] <= #2/15/2017#)  and ([THRUDATE] <=  #2/5/2017# )

 

[sneuberg]

You seem to be looking for what range of dates overlap I suggest setting up your WHERE clause as PBaldy describes here.

 

[Zuzu]

Awesome guys.... thanks very much for the help.

That example you linked to Sneuberg is exactly what i want.

Cheers!

 

[NauticalGent]

Adding this one to the library...Pbaldy has some real pearls on his site.