SELECT query and AND operator

[GAA]

I am selecting my Search Criteria from drop down lists and passing these
selections as variables to my .asp page. I am now trying to write a query
which will return results as per my Search Criteria selected. The problem I
am having is that I want to have the option of only selecting, for example, 2
of the 4 drop down lists and leaving the other one at its default value.
I can get the correct results when selecting 2 of the 4 but when I select
any more than that I am getting sporadic results. It doesn't seem to accept
any more than one AND operator in the one condition.

 

[Stefan B Rusynko]

You can have multiple Ands, but in each case you will need to set a default value for the query (if a dropdown is not selected) or
it will fail w/o a value

--

_____________________________________________
SBR @ ENJOY (-: [ Microsoft MVP - FrontPage ]
"Warning - Using the F1 Key will not break anything!" (-;
To find the best Newsgroup for FrontPage support see:
http://www.frontpagemvps.com/FrontPageNewsGroups/tabid/53/Default.aspx
_____________________________________________


|I am selecting my Search Criteria from drop down lists and passing these
| selections as variables to my .asp page. I am now trying to write a query
| which will return results as per my Search Criteria selected. The problem I
| am having is that I want to have the option of only selecting, for example, 2
| of the 4 drop down lists and leaving the other one at its default value.
| I can get the correct results when selecting 2 of the 4 but when I select
| any more than that I am getting sporadic results. It doesn't seem to accept
| any more than one AND operator in the one condition.
|
|
|

 

[GAA]

Here's the code I'm using, as you'll see it is very simple:
SELECT * FROM Results WHERE
((Name = '%%SelectName%%') AND (Activity = '%%SelectActivity%%')) OR
((Name = '%%SelectName%%') AND (Venue = '%%SelectVenue%%')) OR
((Name = '%%SelectName%%') AND (Purpose = '%%SelectPurpose%%')) OR
((Activity = '%%SelectActivity%%') AND (Venue = '%%SelectVenue%%')) OR
((Activity = '%%SelectActivity%%') AND (Purpose = '%%SelectPurpose%%')) OR
((Venue = '%%SelectVenue%%') AND (Purpose = '%%SelectPurpose%%')) OR
((Name = '%%SelectName%%') AND (Activity = '%%SelectActivity%%') AND (Venue
= '%%SelectVenue%%'))

When comparing two criteria i.e. one AND operator, it works fine, but if
using three criteria i.e. two AND operators (the last part of the query), it
seems to be replacing the second AND with an OR, why I don't know!!

Any ideas?