need help with SQL query

[agmac]

I am trying to run a query off of a form where customers can choose from multiple items to select either a specific item or 'all' items froma a particular field, while being specific in others.. hope I explained that properly..

For example..
Lets say, I have a database called "CARS" with 5 fields in it.. ( Brand, Model, Year, Color, Price )

And The customer chooses to search for:
Brand = "Ford", Model = "f-150", Year = ALL, Color = All and Price = between 10k and 20k

Query:
SELECT * from CARS
WHERE Brand = 'ford' AND Model = 'f-150' AND Year = (this is where my problem lies) AND Color = (again I am having a problem here) AND Price BETWEEN 10000 and 20000

Is there a way that I can get all the results from any particular field to appear while still being dependent on other fields?

I have made a workaround with doing an if..else statement for example..
If YEAR = "All" Then ... Run A different query without the YEAR field in it...

This does end up working... but I end up having like 99 if..else statements each running different queries

Is there another way of doing this...where I can just run one query.

Thanks for reading.

Agmac

 

[gmmastros]

This is going to be a little difficult to explain, so please try to bear with me.

SELECT * 
from   CARS
WHERE  Brand = 'ford' 
       AND Model = 'f-150' 
       AND ('All'='[green]User selection for year[/green]' Or Year = '[green]User selection for year[/green]') 
       AND Color = (again I am having a problem here) 
       AND Price BETWEEN 10000 and 20000

The part in green represents the users selection for year. Let's take a closer look at it.

suppose the user selects 'All'. The query becomes...

('All' = 'All' or Year = 'All')

I assume that year will never = 'All', so, that part will return false, but the expression evaluates to...
(True Or False), which further reduces to True.

Now, suppose the user selects 2006 for year.

('All' = '2006' or Year = '2006')
( False or Year = '2006')

So, only when the year = 2006 will records be returned.

You can, of course, do the same thing for color and your other variables too. As you go through this, make sure you put parenthesis around your OR tests.

[tt]
Where [!]([/!]'All' = Blah or Make = Blah[!])[/!]
And [!]([/!]'All' = Model or Model = Blah[!])[/!]
[/tt]

The position of the parenthesis is VERY important here.

Does this make sense?




-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[agmac]

George,

I think I got it... I am going to try it out...

Thank you very much!

Agmac