SQL problem for filtering > results 1

[credo]

Hello All,
Apologies in advance for the length of the thread...
I'm using Ultradev/ASP and MS Access to allow users to search a db through a web page form. The form allows users to search for a certain sized 'Chalet' within a 'Resort' based on 'ChaletSize'. My SQL resultset returns additional sized Chalets within the resort that sleep 1 or 2 people either side of the user selected number...An example is that a user selects '8'from the dropdown web form for the ChaletSize.. The SQL resultset returns all Chalets + or -2 inclusive.eg. the search retrieves all chalets that sleep 6,7,8,9 and 10.

There are two main tables involved - a ChaletSize table (ChaletSizeID=what gets passed from webpage to SQL, sleep_number=what users see in dropdown) and Chalets (which includes sleeps_from and sleeps_to and ChaletSizeID to allow the tables to be joined as well as several other columns). exampe data for the ChaletSize table is:
ChaletSizeID Sleep_number
1 1
2 2
3 3
...
20 20
21 >20
There are two columns for Chalets (sleeps_from and sleeps_upto)because a Chalet may sleep from 8 and sleep up to 10 etc....My current SQL works correctly except for one option I've included in the web page search from dropdown for ChaletSize... the >20 option.
The SQL I have is quite long and involves other joined tables too, but the WHERE clause is along the lines :
(Tbl_chalets.sleeps_from = 8+2 AND other criteria)OR
(Tbl_chalets.sleeps_from = 8+1 AND other criteria)OR Tbl_chalets.sleeps_from = 8 AND other cirteria)OR
Tbl_chalets.sleeps_from = 8-1 AND other criteria)OR
Tbl_chalets.sleeps_from = 8-2 AND other criteria)OR

So my problem is when a user enters >20 and the webpage passes ID of 21 across I end up with a resultset of 19,20,21,22,23 because +/-2 around 21.What I'm trying to pick up for just this particular option are those chalets that sleep >20 eg, 28,32,40,80 etc...does anyone know how I might achieve this without a lot of restructuring.. ?

 

[tlbroadbent]

First a suggestion. Use BETWEEN to find the size. You can then replace five criteria clauses with one.

(Tbl_chalets.sleeps_from BETWEEN 8-2 AND 8+2)
AND <other criteria>

Now for your main question. You should be able to achieve what you want with the IIF function.

(Tbl_chalets.sleeps_from BETWEEN IIF(ChaletSizeID < 21, <size>-2, <size>) AND IIF(ChaletSizeID < 21, <size>+2, 999))
AND <other criteria> Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[credo]

many thanks Terry - I already changed the SQL to use Between after reading up on some SQL - but thank you for confirming it as a simpler way to achieve the desired result.
I will gave the main problem a shot using IIF. I did read somewhere that this can cause performance problems when used in the Where Clause but I'll try and get it working first....

 

[tlbroadbent]

Using any function in a query criteria may cause slower performance. However, you'll probably not notice much if any degradation in this particualr query. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.