Search By Multiple Criteria

[Bill Case]

I have a library database with each item (book or
whatever) cataloged by multiple criteria. All of the
information is in a single table, call it BOOKS. I would
like to be able to create a form to use combo boxes
and/or text fields to allow users to search for
publications meeting their needs. For example, for a
mechanical engineer might want to look for a publication
based on different things such as the following (I put a
field name after each one):

1. Manufacturer - MANF
2. Product - PROD
3. Representative Name - REPNM
4. Representative Company - REPCO

After the user specifies one or more criteria, the
command button would launch the same simple report
(regardless of the criteria selected), let's call it
RESULTS.

I have connected a combo box to a command button before,
using the following (from a different DB):

-------
Private Sub Command13_Click()

Dim strSQLWhere As String

If IsNull(Me.Combo8) = False Then
strSQLWhere = "StreetName = '" & Me.Combo8 & "'"
End If

DoCmd.OpenReport "Entire List - All Addresses",
acViewPreview, , strSQLWhere
DoCmd.Maximize

End Sub
-------

I don't know how to connect the multiple criteria to the
button, which would basically say "Search".

Any help would be greatly appreciated. Also, apologies
in advance if this should be posted in the Queries
newsgroup.

 

[Gary Miller]

Bill,

One approach for this is to setup something similar to what
you have done for the one combo box for each one with the
following variation. You want to start your strSQL as empty
and then test it's length at each new combobox. If it is 0,
you know that it is empty so you can just add the new Where
item. If the length is greater than 0, you know that there
is already at least one item so you need to add "AND " to
it. By the way, if you are going to have many of these, you
should really rename all the combos to something useful like
'cboStreet' so that you can keep them straight.

Here is how you would redo your code...

Private Sub Command13_Click()

Dim strSQL As String

strSQL = ""

If IsNull(Me!cboStreetName) = False Then
If Len(strSQL) = 0 Then
strSQL = "StreetName = '" & Me.Combo8 & "'"
Else
strSQL = " AND StreetName = '" & Me.Combo8 & "'"
End If
End If

If IsNull(Me!cboState) = False Then
If Len(strSQL) = 0 Then
strSQL = "State = '" & Me.Combo8 & "'"
Else
strSQL = " AND State = '" & Me.Combo8 & "'"
End If
End If

DoCmd.OpenReport "Entire List - All Addresses",
acViewPreview, , strSQL
DoCmd.Maximize

End Sub

Gary Miller
Sisters, OR

 

[Bill Case]

Hi Gary:

Thanks for the wonderful help so far. I've built the
form with four combo boxes, and when I search on one box
alone, it works brilliantly, however, when I search on
more than one box, it returns the following error:

Run-time error '3075':

Syntax error (missing operator) in query expression '(
AND RepCompany = 'CEW Lighting')'.

Here's the code I have in place:

Private Sub Command13_Click()

Dim strSQL As String

strSQL = ""

If IsNull(Me!EManfCombo) = False Then
If Len(strSQL) = 0 Then
strSQL = "Manufacturer = '" & Me.EManfCombo & "'"
Else
strSQL = "Manufacturer = '" & Me.EManfCombo & "'"
End If
End If

If IsNull(Me!EProdCombo) = False Then
If Len(strSQL) = 0 Then
strSQL = "Product = '" & Me.EProdCombo & "'"
Else
strSQL = " AND Product = '" & Me.EProdCombo & "'"
End If
End If

If IsNull(Me!ERepNameCombo) = False Then
If Len(strSQL) = 0 Then
strSQL = "RepName = '" & Me.ERepNameCombo & "'"
Else
strSQL = " AND RepName = '" & Me.ERepNameCombo
& "'"
End If
End If

If IsNull(Me!ERepCoCombo) = False Then
If Len(strSQL) = 0 Then
strSQL = "RepCompany = '" & Me.ERepCoCombo & "'"
Else
strSQL = " AND RepCompany = '" & Me.ERepCoCombo
& "'"
End If
End If

DoCmd.OpenReport "Electrical Catalogs", acViewPreview, ,
strSQL
DoCmd.Maximize

End Sub

... . . . . .

Anything more you can offer will be greatly appreciated.
Again, thanks for getting me this far.
..

 

[Gary Miller]

Bill,

Sorry about that. I must have been a in a bit of a hurry
when I did this. We need to modify all of the lines so they
say...

strSQL = strSQL & ".....

Without that we are just replacing the string, not adding to
it. My fault there. Try this syntax...

If IsNull(Me!EProdCombo) = False Then
If Len(strSQL) = 0 Then
strSQL = strSQL & "Product = '" & Me.EProdCombo & "'"
Else
strSQL = strSQL & " AND Product = '" & Me.EProdCombo
& "'"
End If
End If

Gary Miller
Sisters, OR

 

[Bill Case]

Works brilliantly! Fantastic! Thank you so very much
for your help!

 

[Gary Miller]

Glad to help.

--

Gary Miller
Gary Miller Computer Services
Sisters, OR
________________________