I have the following query on a form of mine. Its a search form and what happens is there are various search options, then when the hit the search button it runs the query. What I am having trouble doing is I want people to be able to select an option under Trade1, but then when they click on the search button, it also checks the Trade2 field and if it is in this field to also appear in the results.
here is the code
CODE
Private Sub cmdSearch_Click()
On Error Resume Next
Dim Ssql As String
Dim sCriteria As String
sCriteria = "WHERE 1=1 "
If Me![GenCompanyName] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSubbie2.GenCompanyName like """ & GenCompanyName & "*"""
End If
If Me![GenFirstName] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSubbie2.GenFirstName like """ & GenFirstName & "*"""
End If
If Me![GenLastName] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSubbie2.GenLastName like """ & GenLastName & "*"""
End If
If Me![GenPostalCity] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSubbie2.GenPostalCity like """ & GenPostalCity & "*"""
End If
If Me![GenTrade] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSubbie2.GenTrade like """ & GenTrade & "*"""
End If
If Me![GenTrade2] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSubbie2.GenTrade2 like """ & GenTrade2 & "*"""
End If
If Me![GenDivision] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSubbie2.GenDivision like """ & GenDivision & "*"""
End If
If Me![CorrFunction] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSubbie2.CorrFunction like """ & CorrFunction & "*"""
End If
If Me![CorrCC] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSubbie2.CorrCC like """ & CorrCC & "*"""
End If
If Me![CorrOther1Text] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSubbie2.CorrOther1text like """ & CorrOther1Text & "*"""
End If
If Me![CorrOther2Text] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSubbie2.CorrOther2text like """ & CorrOther2Text & "*"""
End If
If Nz(DCount("*", "qrySearchCriteriaSubbie2", Right(sCriteria, Len(sCriteria) - 14)), 0) > 0 Then
Ssql = "SELECT DISTINCT [GenCompanyName],[GenFirstName], [GenLastName],[GenPostalCity], " & _
" [GenTrade], [GenTrade2], [genothertrade], [GenDivision], [CorrFunction], [CorrCC], [Corrother1text], " & _
" [CorrOther2text] from qrysearchcriteriasubbie2 " & sCriteria
Forms![frmSearchCriteriaSubbie]![frmSearchCriteriaSubbie2].Form.RecordSource = Ssql
Forms![frmSearchCriteriaSubbie]![frmSearchCriteriaSubbie2].Form.Requery
Else
MsgBox "I'm Sorry, the search failed to find any records that match." & vbCr & vbCr & _
"Please try again!", vbOKOnly + vbQuestion, "Search Record"
Call subClearFields
End If
So I'm assuming that I need to change something for the part here:
If Me![GenTrade] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSubbie2.GenTrade like """ & GenTrade & "*"""
End If
If Me![GenTrade2] <> "" Then
sCriteria = sCriteria & " AND qrySearchCriteriaSubbie2.GenTrade2 like """ & GenTrade2 & "*"""
End If
Basically at this point I want it to say If the option selected in Trade is found in GenTrade or GenTrade2 to list the result, likewise I would then like that if they selected a trade in GenTrade2 that it also checks GenTrade and lists all the results.
Any ideas on how to get this to do this? I have tried a few things but am now blocked and can't figure it out.
Thanks in advance
Megan
Edited to prevent page width expansion.
Edited by: VanThienDinh on Tue Sep 15 3:58:35 EDT 2009.