Help w/Access Search Form

[M22664]

I have a new Access database I'm working on. There is a form called ProjectForm that is used for entering project specific information. The information entered goes to a table called ProjectTable. At the bottom of that ProjectForm is a button that reads "Search Projects" when you click on that button a Search Form pops up called frmSearch. The Search Form has three combo boxes and three texted boxes where the user can enter three different search fields and search strings to search all the projects in the database to see for example how many projects we used a certain widget. At the bottom of the Search Form is a button that reads "Search". The code below is the Event associated with the "Search" button. My problem is the search does not work when search fields and search strings are entered in all three combo boxes and text boxes. It only works when search criteria is selected with the first combo box/text box.

Can you look at the code below and determine what the problem is.

Thank you.


Private Sub cmdSearch_Click()
If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
MsgBox "You must select a field to search."

ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must enter a search string."

Else

'Generate search criteria
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"
'GCriteria = cboSearchField2.Value & " LIKE '*" & txtSearchString2 & "*'"
'GCriteria = cboSearchField3.Value & " LIKE '*" & txtSearchString3 & "*'"


'Filter ProjectForm based on search criteria
Form_ProjectForm.RecordSource = "select * from ProjectTable where " & GCriteria
Form_ProjectForm.Caption = "ProjectTable (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"
'Form_ProjectForm.Caption = "ProjectTable (" & cboSearchField2.Value & " contains '*" & txtSearchString2 & "*')"
'Form_ProjectForm.Caption = "ProjectTable (" & cboSearchField3.Value & " contains '*" & txtSearchString3 & "*')"

'Close frmSearch
DoCmd.Close acForm, "frmSearch"

MsgBox "Results have been filtered."

End If


End Sub

[OBP]

If you want the Filter to look for more than one item then you need to join the fields using an AND like this version that uses the items selected in a Multi select List Box, note the final section that removes the last AND because it won't be used. For Each itm In Me.lstFilterByArea.ItemsSelected
If strArea = "" Then
strArea = "strFunctionalArea = '" & Me.lstFilterByArea.ItemData(itm) & "'"
Else
strArea = strArea & " OR strFunctionalArea = '" & Me.lstFilterByArea.ItemData(itm) & "'"
End If
Next itm
If Not strArea = "" Then
strArea = " (" & strArea & ") AND "
End If

strFilter = strType & strCritical & strScope & strRRB1 & strRRB2 & strKPP & strArea
If Not strFilter = "" Then
strFilter = Left(strFilter, Len(strFilter) - 5)
End If

[M22664]

I'm sorry, I really new to VBA. Would you be able to show where the "AND" would go in the code in my initial post?

Thanks

[OBP]

I am not familiar with your version of a Search string, but I would have thought soemthing like this should work.
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*' AND "
GCriteria = GCriteria & cboSearchField2.Value & " LIKE '*" & txtSearchString2 & "*' AND "
GCriteria = GCriteria & cboSearchField3.Value & " LIKE '*" & txtSearchString3 & "*'"

I normally have the Search Fields on a mainform and use the Search SQL to set a Filter on a Subform which displays the actual data.

[M22664]

OBP:

Thanks for you help. One question, can I substitute "AND" for "OR" if I want for one search field or 2 search fields or all three search fields?

[OBP]

Yes you can, and of course you can mix them, but you have to be careful to include all the ANDs you need where you both items before using the OR.
Play around with is the best way to learn.

[M22664]

I changed the code to this:


'Generate search criteria
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*' OR "
GCriteria = GCriteria & cboSearchField2.Value & " LIKE '*" & txtSearchString2 & "*' OR "
GCriteria = GCriteria & cboSearchField3.Value & " LIKE '*" & txtSearchString3 & "*'"

'Filter ProjectForm based on search criteria
Form_ProjectForm.RecordSource = "select * from ProjectTable where " & GCriteria
Form_ProjectForm.Caption = "ProjectTable (" & cboSearchField.Value & " contains '*" & txtSearchString & "*') OR "
Form_ProjectForm.Caption = "ProjectTable (" & cboSearchField2.Value & " contains '*" & txtSearchString2 & "*') OR "
Form_ProjectForm.Caption = "ProjectTable (" & cboSearchField3.Value & " contains '*" & txtSearchString3 & "*')"

I tried searching for only one parameter and I get the following error:

Run-time error '3075'
Syntax error (missing operator) in query expression 'VesselQty LIKE '*4*" OR LIKE '**" OR LIKE '**"

Seems like it works if I use all three search fields, but if I use only one or two, I get the error above.

[OBP]

Did you include the last section that I mentioned that removes the Or or And when there is no second or third search field?

If Not strFilter = "" Then
strFilter = Left(strFilter, Len(strFilter) - 5)
End If

It is 5 characters for an And and 4 for an OR

[M22664]

In my case is this correct:

strFilter = strType & cboSearchField & cboSearchField2 & cboSearchField3
If Not strFilter = "" Then
strFilter = Left(strFilter, Len(strFilter) - 4)
End If

What is strType?

[OBP]

this line
strFilter = strType & cboSearchField & cboSearchField2 & cboSearchField3
is used when setting the Form's Filter to the Search fields, you should replace the strFilter with your GCriteria.