Re: Trouble with a multiple parameter search

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi Mary

What you are trying to do makes perfect sense, but I think it might be
easier to take a little step further and turn this into a search form. This
involves a bit of code, but is much simpler, more flexible, and more
efficient in the long run. For example, if you only used 2 of the criteria
boxes, the code will produce a SQL statement that only has 2 phrases in the
WHERE clause instead of 14 like the one you posted.

I suggest a continuous form where the search results are shown one per row.
The criteria boxes go in the Form Header section (View menu in form design),
along with a command button to execute the search. The search button builds
the SQL statement from the non-blank boxes, and applies it as the
RecordSource of the search form, so that it shows only matches as its
results.

The example below assumes the search criteria text boxes are named
txtFindTP, txtFindSPKR, etc. The code concatenates each non-null search
value into the string, tacking " AND " on the end ready for the next one.
The final trailing " AND " is then chopped off, and the string is added to
the stub of the SQL statement. (Note that the constant is one long line,
starting with SELECT and ending with the FROM clause."

Private Sub cmdSearch_Click()
Dim strSql As String
Dim lngLen As Long
Const strcStub = "SELECT [Bridge Transcripts].TP, [Bridge
Transcripts].TC, [Bridge Transcripts].SPKR, [Bridge Transcripts].TRANSCRIPT,
[Bridge Transcripts].STARS, [Bridge Transcripts].WITH, [Bridge
Transcripts].KEYWORDS, [Bridge Transcripts].LOC, [Bridge Transcripts].ID
FROM [Bridge Transcripts]"

'Build the WHERE clause from the non-Null boxes.
If Not IsNull(Me.txtFindTP) Then
strSql = strSql & "(TP = """ & Me.txtFindTP & """) AND "
End If

If Not IsNull(Me.txtFindSpkr) Then
strSql = strSql & "(SPKR = """ & Me.txtFindSPKR & ") AND "
End If

'etc for other text boxes.

lngLen = Len(strSql) - 5 'Chop off the trailiing " AND ".
If lngLen > 0 Then
strSql = strcStub & " WHERE " & Left$(strSql, lngLen) & ";"
Else
strSql = strcStub & ";"
MsgBox "No criteria"
End If

'Finally, show the search results in this form.
Me.RecordSource = strSql
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<maryrb@xxxxxxxxx> wrote in message
news:1136254512.856277.288170@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>
> I'm very new to Access, and have been struggling to get what seems to
> me a very simple database to work correctly.
>
> I've got just one table, and each record contains only nine fields
> (including the primary key). The fields are all text, except for one
> that is memo.
>
> What I'd like to be able to do is build a form with input boxes for
> seven of the fields that will launch a multiple parameter query. I'll
> never be putting search terms into all of the fields on the form, but
> I'll often be using different fields for different searches.
>
> Seems like a fairly straightforward project. But I'm having trouble
> with the query. Even without using the form, it will only return proper
> results on data entered into the first two parameters. No results are
> returned for anything that I enter into the last five parameters
> (although i do get proper returns when i build simple single-parameter
> queries for each of the fields).
>
> So ... here's the SQL I've got. From all the SQL stuff i've been poring
> over, I think I'm using AND correctly, but Access obviously doesn't
> think so.
>
> SELECT [Bridge Transcripts].TP, [Bridge Transcripts].TC, [Bridge
> Transcripts].SPKR, [Bridge Transcripts].TRANSCRIPT, [Bridge
> Transcripts].STARS, [Bridge Transcripts].WITH, [Bridge
> Transcripts].KEYWORDS, [Bridge Transcripts].LOC, [Bridge
> Transcripts].ID
> FROM [Bridge Transcripts]
>
> WHERE ([Bridge Transcripts].[TP] = [Forms]![BTF]![TP] Or
> [Forms]![BTF]![TP] Is Null)
> AND ([Bridge Transcripts].[SPKR] = [Forms]![BTF]![SPKR] Or
> [Forms]![BTF]![SPKR] Is Null)
> AND ([Bridge Transcripts].[TRANSCRIPT] = [Forms]![BTF]![TP] Or
> [Forms]![BTF]![TRANSCRIPT] Is Null)
> AND ([Bridge Transcripts].[STARS] = [Forms]![BTF]![TP] Or
> [Forms]![BTF]![STARS] Is Null)
> AND ([Bridge Transcripts].[WITH] = [Forms]![BTF]![TP] Or
> [Forms]![BTF]![WITH] Is Null)
> AND ([Bridge Transcripts].[KEYWORDS]= [Forms]![BTF]![TP] Or
> [Forms]![BTF]![KEYWORDS] Is Null)
> AND ([Bridge Transcripts].[LOC] = [Forms]![BTF]![TP] Or
> [Forms]![BTF]![LOC] Is Null);
>
> Any help greatly appreciated.
>
> -Mary


.

Relevant Pages

  • Re: Help with SQL statement
    ... > Dim Product As String, TarWgt As Double, FileNum As ... > Exit Sub ... Your SQL statement doesn't work because it is not an UPDATE you need to ... directly from the combo box's Columnto the other text boxes, ...
    (microsoft.public.access.modulesdaovba)
  • Re: Unable to reset Combo Box Data Field Format.
    ... Regards to mixed data types and the combo boxes - ... controls from the target SQL statement. ... > either selecting values from the combo box row source or by typing them> it ... > based on other combo box selections of fields in the DB. ...
    (microsoft.public.access.forms)
  • Re: Combo Box migration problem, from 2000 to 2003
    ... FROM tblCLASSIFICATIONS; ... There was a bug for combo boxes in A2003 with one of the servic packs - ... When viewing or converting to 2003, ... one combo box has the following SQL statement: ...
    (microsoft.public.access.forms)
  • Re: Multiple values passed to UDF with two parameters
    ... I first parsed all the check boxes to see which were selected and ... then made the WHERE clause. ... Dim strSite As String ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Use * in SQL if Text Box is empty
    ... I have an SQL Statement that have several text boxes in the WHERE clause. ... there a simple way of making Access ignore the text boxes that are empty ...
    (microsoft.public.access.formscoding)