Parameter Query between two date fields

[Pyro]

Hi,

I have been looking everywhere, and i can't find a solution to my problem, so i'm hoping some one here might be able to help.

I have a table, tblmedia which has several fields, but for this i am only interested in:

prodname (text field) relates to a product name.
lotno (text field) relates to a lot number for a product.
in_use (date/time filed) which lists when a batch of this product was put in to use.
out_use (date/time field) which lists when the above batch was finished.

What i am trying to do is create a parameter query that allows me to view what products and lots were in use between two dates, for example 10/03/2009 and 15/03/2009 (dd/mm/yyyy). Here is the sql that i have so far:

SELECT prodname, lotno, in_use, out_use
FROM tblmedia
WHERE (((in_use)>=[Enter Start Date] And (in_use) Is Not Null) AND ((out_use)<=[Enter Finish Date] And (out_use) Is Not Null));

The problem with this is that it will only return values for products that were put into use after the first date that i enter and finished before the second date that i enter.

What i want it to do is... the best way to describe this is with an example. Lets say that i put a product into use on the 05/03/2009 and finished it on the 13/03/2009 (dd/mm/yyyy), and i run a query with start date 10/03/2009 and finish date 15/03/2009 (dd/mm/yyyy). I want the above product to be included because it was still in use through part of that period.

Any suggestions?

Thanks,

Ben.

 

[pbaldy]

Searching on booking will probably turn up exact SQL. Basically, you want to test that the start date in the table is before or equal to your test end date, and the end date in the table is after or equal to your test start date. It sounds funny at first, but it will find all overlapping records.

 

[Pyro]

Thanks pbaldy, i looked but i couldn't find any good sql examples, but you got me thinking on the right path and i solved the problem. This is what worked:

SELECT prodname, lotno, in_use, out_use
FROM tblmedia
WHERE (((out_use)>=[Enter Start Date] And (out_use) Is Not Null) AND ((in_use)<=[Enter Finish Date] And (in_use) Is Not Null));

Always satisfying to get something like this to work after you've been playing around with it for a while.