I am trying to search my database using two search criteria. I have no problem with one but am now having to input SQL into the advanced setting.

I know I will have to incorporate the where statement but am not having AAAAAAny! luck

Thanks again in advance!!
Brian

Following example assumes that the values from the Drop down boxes are text strings.

Drop down box 1 - selFirstCriteria
Drop down box 2 - selSecondCriteria

Table:

RecordID     FirstField     SecondField     ThirdField


On the results page create your Recordset in Simple view selecting the fields you want to display.

In the search results page before the recordset code:
<pre id=code><font face=courier size=2 id=code>
&lt;%
varOne = Request("selFirstCriteria"
varTwo = Request("selSecondCriteria"</font id=code></pre id=code>
<pre id=code><font face=courier size=2 id=code>
WhereString = "WHERE RecordID &lt;&gt; -1"</font id=code></pre id=code>
<pre id=code><font face=courier size=2 id=code>
If varOne &lt;&gt; "" Then
AndFirstString = "AND FirstField LIKE '%" & varOne & "%'"
Else AndFirstString = ""
End If</font id=code></pre id=code>
<pre id=code><font face=courier size=2 id=code>
If varTwo &lt;&gt; "" Then
AndSecondString = "AND SecondField LIKE '%" & varTwo & "%'"
Else AndSecondString = ""
End If
%&gt;
</font id=code></pre id=code>

Then find the SELECT statement in the recordset code, somthing like:
<pre id=code><font face=courier size=2 id=code>
YourRecordsetName.Source = "SELECT FirstField, SecondField, ThirdField FROM YourTable"
</font id=code></pre id=code>

Modify it as follows:
<pre id=code><font face=courier size=2 id=code>
YourRecordsetName.Source = "SELECT FirstField, SecondField, ThirdField FROM TextTable " & WhereString & AndFirstString & AndSecondString
</font id=code></pre id=code>

If the values of the drop down boxes are numeric change the AndStrings for both AndFirstString and AndSecondSTring, for example:

Change "AND FirstField LIKE '%" & varOne & "%'" to "AND FirstField =" & varOne


If RecordID is the Autonumber Field The "WHERE RecordID &lt;&gt; -1" will select all records, as none will have an ID of <b>-1</b>

Using this method you can add as many drop down boxes as you wish to the search page then pick up the value with another Request("DropDownBoxName" create another AndString and add to the end of the SELECT statement.

You can take a look at the tutorial link at the bottom of the page for more examples.

Regards

Owen.

Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo

Edited by - oeastwick on 09/22/2001 04:03:16

Hey, I've just discovered that the forum automatically censors swear words and replaces them with asterisks.

In my previous post I originally called the variables <b>varOne</b> and <b>varTwo</b> - <b>varFirst</b> and, you guesed it <b>var Second</b> without the space obviously.

Took me a while to work out why it displayed as v****cond - yup, it completely innocently contains the word A-R-S-E.

Surely this can be rectified so that it only looks for words when they are not surrounded by other letters.

It's not fucking fair that I should have to change my bloody variable names just so they don't piss off the automatic fucking censor.<img src=../images/dmxzone/forum/icon_smile_big.gif border=0 align=middle>


Mind you it's not that good, it's let me get away with two in my prvious sentence.


Owen.

Edited by - oeastwick on 09/22/2001 04:19:39