I'm building a car parts web site where users will advertise and specify year ranges for their parts, like muffler for Honda's from 1996-2000. On the search page you can specify year range for your search, 1993-1997. My problem is that I'm not getting the list returned incorrectly.

There seems to be four different states in the search.
For instance, the database lists Honda mufflers 1996-2000.
1. Listed date range is totally within the query range- 1994-2003.
2. Listed date range falls within the upper limit of the search- 1998-2003.
3. Listed date range falls within the lower limit of the search- 1990-1996.
4. The query date range falls totally with the listed date range- 1997-1998.

In simpler terms, I need to find out if any part of the listed date range(by way of two columns in the database- FromYear and ToYear) is within the specified search criteria. And be able to pull those records.

I've been using different combinations of >=, <= and the "BETWEEN" function with varying results.

Any help will be greatly appreciated.

Thank you,
Richard Stelma

I'm not sure I understand what difficulty you are having...

If I understand your problem correctly, I'll assume the following:

1. Your products table contains two columns I'll call 'PStart' and 'PEnd' which are integer (not datetime) columns containing a year value (e.g. 2003) and which represent the years of availability for the part. I'll further assume that a table constraint exists on these two columns such that PStart<=PEnd.

2. The query has two parameters '@QStart' and '@QEnd' which are the requested date ranges. These are also integers in the same format as the product years. The client application has insured that these two dates obey the constraint that @QStart<=@QEnd.

If you analyze how the query year values relate to the product year values, there are 6 possible combinations, of which 4 are valid (2 represent the query ranges totally before or totally after the product availability range, so these products should not be selected.

I think the query should be something like:
Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Hi Jeff,

Thank you so much for your reply.

You did understand perfectly what I am trying to do, even adding the two cases where the search range is completely out of range of the target, being either totally above or totally below.

Your suggestion worked perfectly and I understand why you couldn't understand what I was struggling with because, yeah, it seems pretty simple now, to me too, just test for each one of the four conditions. If any of them are true, meeting the conditions of the search, pull the record.

Its good to see you're still with this group.

Thank you.

Cheers,
Richard

I didn't realize it looked like I had left ... :D

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Just cause the group was gone and wasn't sure if it would come back. Really have enjoyed this group and all the great info shared.

Anyway, fashioned the query and all is well.

Thanks,
Richard