I have a form the user enters criteria on that searches a table for information with a query that I need to add date parameters to. I have an idea how to do it but can't quite get the syntax to work properly.
Private Sub Search_Click()
Dim trainerList As String, schoolList As String, townList As String, sportList As String, bodypartList As String, injList As String, sourceList As String
Dim item As Variant, whereClause As String
'loop thru each combo box and build a comma separated list of selected options
'check for (All) and clear criteria when present
For Each item In trainer.ItemsSelected
If trainerList <> "" Then trainerList = trainerList & ","
trainerList = trainerList & "'" & trainer.ItemData(item) & "'"
If trainer.ItemData(item) = "All" Then
trainerList = ""
Exit For
End If
Next
For Each item In school.ItemsSelected
If schoolList <> "" Then schoolList = schoolList & ","
schoolList = schoolList & "'" & school.ItemData(item) & "'"
If school.ItemData(item) = "All" Then
schoolList = ""
Exit For
End If
Next
For Each item In town.ItemsSelected
If townList <> "" Then townList = townList & ","
townList = townList & "'" & town.ItemData(item) & "'"
If town.ItemData(item) = "All" Then
townList = ""
Exit For
End If
Next
For Each item In sport.ItemsSelected
If sportList <> "" Then sportList = sportList & ","
sportList = sportList & "'" & sport.ItemData(item) & "'"
If sport.ItemData(item) = "All" Then
sportList = ""
Exit For
End If
Next
For Each item In bodypart.ItemsSelected
If bodypartList <> "" Then bodypartList = bodypartList & ","
bodypartList = bodypartList & "'" & bodypart.ItemData(item) & "'"
If bodypart.ItemData(item) = "All" Then
bodypartList = ""
Exit For
End If
Next
For Each item In inj.ItemsSelected
If injList <> "" Then injList = injList & ","
injList = injList & "'" & inj.ItemData(item) & "'"
If inj.ItemData(item) = "All" Then
injList = ""
Exit For
End If
Next
For Each item In Source.ItemsSelected
If Source <> "" Then sourceList = sourceList & ","
sourceList = sourceList & "'" & Source.ItemData(item) & "'"
If Source.ItemData(item) = "All" Then
sourceList = ""
Exit For
End If
Next
'now we will build a where clause with fields mapped to SearchQuery
'if list is empty, this means that "All" was selected so no criteria is needed;
'do not add to whereclause
If trainerList <> "" Then
If whereClause <> "" Then whereClause = whereClause & " and "
whereClause = whereClause & " trainer in (" & trainerList & " "
End If
If schoolList <> "" Then
If whereClause <> "" Then whereClause = whereClause & " and "
whereClause = whereClause & " school in (" & schoolList & " "
End If
If townList <> "" Then
If whereClause <> "" Then whereClause = whereClause & " and "
whereClause = whereClause & " town in (" & townList & " "
End If
If sportList <> "" Then
If whereClause <> "" Then whereClause = whereClause & " and "
whereClause = whereClause & " sport in (" & sportList & " "
End If
If bodypartList <> "" Then
If whereClause <> "" Then whereClause = whereClause & " and "
whereClause = whereClause & " bodypart in (" & bodypartList & " "
End If
If injList <> "" Then
If whereClause <> "" Then whereClause = whereClause & " and "
whereClause = whereClause & " inj in (" & injList & " "
End If
If sourceList <> "" Then
If whereClause <> "" Then whereClause = whereClause & " and "
whereClause = whereClause & " Source in (" & sourceList & " "
End If
'MsgBox whereClause
DoCmd.OpenReport "rptSearchQuery", acViewPreview, , whereClause
End Sub
Can someone lend me a hand? Thanks in advance.
Paul
Private Sub Search_Click()
Dim trainerList As String, schoolList As String, townList As String, sportList As String, bodypartList As String, injList As String, sourceList As String
Dim item As Variant, whereClause As String
'loop thru each combo box and build a comma separated list of selected options
'check for (All) and clear criteria when present
For Each item In trainer.ItemsSelected
If trainerList <> "" Then trainerList = trainerList & ","
trainerList = trainerList & "'" & trainer.ItemData(item) & "'"
If trainer.ItemData(item) = "All" Then
trainerList = ""
Exit For
End If
Next
For Each item In school.ItemsSelected
If schoolList <> "" Then schoolList = schoolList & ","
schoolList = schoolList & "'" & school.ItemData(item) & "'"
If school.ItemData(item) = "All" Then
schoolList = ""
Exit For
End If
Next
For Each item In town.ItemsSelected
If townList <> "" Then townList = townList & ","
townList = townList & "'" & town.ItemData(item) & "'"
If town.ItemData(item) = "All" Then
townList = ""
Exit For
End If
Next
For Each item In sport.ItemsSelected
If sportList <> "" Then sportList = sportList & ","
sportList = sportList & "'" & sport.ItemData(item) & "'"
If sport.ItemData(item) = "All" Then
sportList = ""
Exit For
End If
Next
For Each item In bodypart.ItemsSelected
If bodypartList <> "" Then bodypartList = bodypartList & ","
bodypartList = bodypartList & "'" & bodypart.ItemData(item) & "'"
If bodypart.ItemData(item) = "All" Then
bodypartList = ""
Exit For
End If
Next
For Each item In inj.ItemsSelected
If injList <> "" Then injList = injList & ","
injList = injList & "'" & inj.ItemData(item) & "'"
If inj.ItemData(item) = "All" Then
injList = ""
Exit For
End If
Next
For Each item In Source.ItemsSelected
If Source <> "" Then sourceList = sourceList & ","
sourceList = sourceList & "'" & Source.ItemData(item) & "'"
If Source.ItemData(item) = "All" Then
sourceList = ""
Exit For
End If
Next
'now we will build a where clause with fields mapped to SearchQuery
'if list is empty, this means that "All" was selected so no criteria is needed;
'do not add to whereclause
If trainerList <> "" Then
If whereClause <> "" Then whereClause = whereClause & " and "
whereClause = whereClause & " trainer in (" & trainerList & " "
End If
If schoolList <> "" Then
If whereClause <> "" Then whereClause = whereClause & " and "
whereClause = whereClause & " school in (" & schoolList & " "
End If
If townList <> "" Then
If whereClause <> "" Then whereClause = whereClause & " and "
whereClause = whereClause & " town in (" & townList & " "
End If
If sportList <> "" Then
If whereClause <> "" Then whereClause = whereClause & " and "
whereClause = whereClause & " sport in (" & sportList & " "
End If
If bodypartList <> "" Then
If whereClause <> "" Then whereClause = whereClause & " and "
whereClause = whereClause & " bodypart in (" & bodypartList & " "
End If
If injList <> "" Then
If whereClause <> "" Then whereClause = whereClause & " and "
whereClause = whereClause & " inj in (" & injList & " "
End If
If sourceList <> "" Then
If whereClause <> "" Then whereClause = whereClause & " and "
whereClause = whereClause & " Source in (" & sourceList & " "
End If
'MsgBox whereClause
DoCmd.OpenReport "rptSearchQuery", acViewPreview, , whereClause
End Sub
Can someone lend me a hand? Thanks in advance.
Paul