building query depending on options selected

[tyutghf]

OK, hope I manage to explain this ok...

I have a form with a dropdown allowing users to select an area (dropdown list so they can only select 1).

There are then checkboxes with types of places in that area which they can select multiple.

Here are the scenarios

if they select nothing the entire database is returned
they can select an area and no place types which returns everything in that area
they can select no area but as many place types as they wish that will bring back all results of place type selected for all areas

these all work ok with my code, BUT the final scenario is where I am stuck

they can select an area and multiple place types which will bring up the place types selected from only that area selected.

Instead of working it is creating the following SQL

SELECT * FROM places WHERE town = 'Bakewell' AND coffeeshop = '1' OR deli = '1' OR eatery = '1' OR giftshop = '1' OR interior = '1' order by placename

as you can see the OR's are causing the problem.

I have stared at this for hours now and can`t get it to work, can someone cast an eye over it please?

Here's the code...

SQL = "SELECT * FROM places "
			
			strNext = 0
			strTown = 0
			strAnd = 0
			
			if request.querystring("area") <> "" then
			strNext = 1
			strTown = 1
			SQL = SQL & "WHERE town = '"&request.querystring("area")&"' "
			end if
			
			if request.querystring("typeCoffee") <> "" then
			   if strNext = 0 then
						SQL = SQL & "WHERE coffeeshop = '1' "
						else
						  if strTown = 0 or strAnd = 1 then
						  SQL = SQL & "OR coffeeshop = '1' "
								else
								SQL = SQL & "AND coffeeshop = '1' "
								strAnd = 1
								end if
						end if
						strNext = 1
			end if
			
			if request.querystring("typeGallery") <> "" then
			   if strNext = 0 then
						SQL = SQL & "WHERE gallery = '1' "
						else
						  if strTown = 0 or strAnd = 1 then
						  SQL = SQL & "OR gallery = '1' "
								else
								SQL = SQL & "AND gallery = '1' "
								strAnd = 1
								end if
						end if
						strNext = 1
			end if
			
			if request.querystring("typeDelicatessen") <> "" then
			   if strNext = 0 then
						SQL = SQL & "WHERE deli = '1' "
						else
						  if strTown = 0 or strAnd = 1 then
						  SQL = SQL & "OR deli = '1' "
								else
								SQL = SQL & "AND deli = '1' "
								strAnd = 1
								end if
						end if
						strNext = 1
			end if
			
			if request.querystring("typeArtist") <> "" then
			   if strNext = 0 then
						SQL = SQL & "WHERE localartist = '1' "
						else
						  if strTown = 0 or strAnd = 1 then
						  SQL = SQL & "OR localartist = '1' "
								else
								SQL = SQL & "AND localartist = '1' "
								strAnd = 1
								end if
						end if
						strNext = 1
			end if
			
			if request.querystring("typeEatery") <> "" then
			   if strNext = 0 then
						SQL = SQL & "WHERE eatery = '1' "
						else
						  if strTown = 0 or strAnd = 1 then
						  SQL = SQL & "OR eatery = '1' "
								else
								SQL = SQL & "AND eatery = '1' "
								strAnd = 1
								end if
						end if
						strNext = 1
			end if
			
			if request.querystring("typeFarmshop") <> "" then
			   if strNext = 0 then
						SQL = SQL & "WHERE farmshop = '1' "
						else
						  if strTown = 0 or strAnd = 1 then
						  SQL = SQL & "OR farmshop = '1' "
								else
								SQL = SQL & "AND farmshop = '1' "
								strAnd = 1
								end if
						end if
						strNext = 1
			end if
			
			if request.querystring("typeGiftshop") <> "" then
			   if strNext = 0 then
						SQL = SQL & "WHERE giftshop = '1' "
						else
						  if strTown = 0 or strAnd = 1 then
						  SQL = SQL & "OR giftshop = '1' "
								else
								SQL = SQL & "AND giftshop = '1' "
								strAnd = 1
								end if
						end if
						strNext = 1
			end if
			
			if request.querystring("typeChocshop") <> "" then
			   if strNext = 0 then
						SQL = SQL & "WHERE chocolateshop = '1' "
						else
						  if strTown = 0 or strAnd = 1 then
						  SQL = SQL & "OR chocolateshop = '1' "
								else
								SQL = SQL & "AND chocolateshop = '1' "
								strAnd = 1
								end if
						end if
						strNext = 1
			end if
			
			if request.querystring("typeHome") <> "" then
			   if strNext = 0 then
						SQL = SQL & "WHERE interior = '1' "
						else
						  if strTown = 0 or strAnd = 1 then
						  SQL = SQL & "OR interior = '1' "
								else
								SQL = SQL & "AND interior = '1' "
								strAnd = 1
								end if
						end if
						strNext = 1
			end if
			
			if request.querystring("typeRestaurant") <> "" then
			   if strNext = 0 then
						SQL = SQL & "WHERE restaurant = '1' "
						else
						  if strTown = 0 or strAnd = 1 then
						  SQL = SQL & "OR restaurant = '1' "
								else
								SQL = SQL & "AND restaurant = '1' "
								strAnd = 1
								end if
						end if
						strNext = 1
			end if
			
			if request.querystring("typeJeweleryshop") <> "" then
			   if strNext = 0 then
						SQL = SQL & "WHERE jeweleryshop = '1' "
						else
						  if strTown = 0 or strAnd = 1 then
						  SQL = SQL & "OR jeweleryshop = '1' "
								else
								SQL = SQL & "AND jeweleryshop = '1' "
								strAnd = 1
								end if
						end if
						strNext = 1
			end if
			
			SQL = SQL & " order by placename"

 

[wvdba]

that's because your and's and or's are mixed up.
you have to use parentheses to clarify your results.
example:
you want to retrieve any records that has city = washington
and coffee shop = 1 or 2 or 3
sql:
select * from mydb where city='washington' and
(city = 1 or city = 2 or city = 3)