Change WHERE criteria depending on incoming variable

[danb]

Okay, this is a really tricky one...

I have a table containing shop names and various 'check-box' attributes such as 'SellsClothing', 'SellsBooks', 'SellsFood', etc. I have an ASP page requesting particular shops according to these criteria. Variable criteria are passed into the query from the ASP page as either yes/no (1/0) values:

SELECT * FROM Shops WHERE IsActive=1 AND SellsClothing=[SellsClothing_IN] AND SellsBooks=[SellsBooks_IN] AND SellsFood=[SellsFood_IN]

Trouble is, I need to only add the WHERE criteria if particular values are put in.

For example someone might choose to view all records containing 'yes' values for SellsBooks, SellsClothing and SellsFood.
In this instance, all WHERE criteria need to be satisfied as being all 'yes'. That's fine. However, if someone only wants to see shops that sell clothing, I can't put all the WHERE clauses in because some shops might sells books too. In this instance, it must ignore the SellsBooks category - *not* give it a 'No' value - since it does actually sell books, but the user doesn't want to see that.

Does this make sense? The user needs to be able to either select 'Any' (all clauses need to be put in with OR values) or select based on just one of the criteria.

I think I need something like:

SELECT * FROM Shops WHERE IsActive=1
If([Products_IN] like 'Books') then say AND SellsBooks=[SellsBooks_IN]
else if ([Products_IN] like 'Food') then say AND SellsFood=[SellsFood_IN]
else if ([Products_IN] like 'Clothes') then say AND SellsClothes=[SellsClothes_IN]
else if ([Products_IN] like 'Any') then say AND SellsBooks=[SellsBooks_IN] OR SellsClothes=[SellsClothes_IN] OR SellsFood=[SellsFood_IN])


Can anyone out there please help me??

Thank you so much.

D

 

[FoFa]

Typically you would build your SQL in a variable, that way you can control the where clause.

strSQL = "SELECT * FROM SHOPS WHERE IsActive = 1"
if ([Products_IN]) then
strSQL = strSQL & " AND mycol like 'Books*'"
end if

And so on

 

[Pat Hartman]

If your table is Jet, the True value is -1 NOT 1.

 

[danb]

Okay, thanks, that's that bit sorted. You'll need to bear with me as I am but a novice.

I still have no idea how to put conditional clauses into a SQL select. I've read up about it on some sites, but to be honest nothing is making too much sense.

I think it might be something like:

SELECT * FROM Shops WHERE IsActive=-1
iif(SellsBooks_IN=-1, AND SellsBooks=-1)
iif(SellsClothing_IN=-1, AND SellsClothing=-1)
etc.

Is what I'm trying to do even possible? There's a deadline looming, so if anyone has any advice, I'd be totally grateful.

Thankx