date range query

[sun_after_rain]

I am trying to extract records that fall within a certain date range.The date field is in the format mm/dd/yyyy.

Here's the tricky part:
The records also have data ranges. For example: Movie A has a start_date of 4/1/2005 and end_date of 3/31/2013.

The user can search on start date and/or end date.

If the search date is 4/1/2005, Movie A will result. If the search date is 4/1/2005 and end date 4/01/2013, Movie A will result. If Movie A end_date is 4/01/2013, Movie A will result. And if start date is 3/31/2013 and end_date is 4/01/2014, Movie A will result.

How do I write a code for this?

Puzzled.

 

[pbaldy]

Does this help?

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

 

[sun_after_rain]

do the dates need to be in a certain format?

 

[pbaldy]

In a query I've only tested with US format. Generally, in VBA code Access wants the date in US format, but in other areas it should follow the regional settings.