Private Sub cmdSearch_Click()
Me.lstCustInfo.Visible = True
Me.Text43.Visible = True
Me.Label45.Visible = True
Me.Command135.Enabled = True
Me.Command137.Enabled = True
Dim strSQLHead As String
'Dim strSQLWhere As String
' Dim strOrder As String
'Dim strSQL As String
Dim strJoin As String
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.
'Set the Dimensions of the Module
Dim strSql As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As querydef
Set dbNm = CurrentDb()
Dim sSql As String
Dim sCriteria As String
sCriteria = "WHERE "
'Constant Select statement for the RowSource
'strSQL = "SELECT clients.clientID, clients.FirstName, clients.surName, clients.Street, clients.Postcode, clients.homephone, clients.LifelineiD, clients.lifelineserial " & _
'"FROM clients"
strSql = "SELECT tblmain.auto, tblmain.log,tblmain.message,tblmain.datee, tblmain.areacode, tblmain.incidenttype, tblmain.DirectSurveillence, tblmain.Policemonitor, tblmain.policeresponce,tblmain.Arrests ,tblmain.realtimenumber,tblmain.reason,tblmain.operator,tblmain.reportedby ,tblmain.cadnum " & _
"FROM tblmain"
strWhere = "WHERE"
strOrder = "ORDER BY tblmain.log;"
strJoin = " AND "
'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.Text96) Then '<--If the textbox txtFirstName contains no data THEN do nothing
strWhere = strWhere & " (tblmain.operator) Like '*" & Me.Text96 & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If
'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.Text111) Then '<--If the textbox txtFirstName contains no data THEN do nothing
strWhere = strWhere & " (tblmain.message) Like '*" & Me.Text111 & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If
'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.Text130) Then '<--If the textbox txtFirstName contains no data THEN do nothing
strWhere = strWhere & " (tblmain.areacode) Like '*" & Me.Text130 & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If
'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.Text147) Then '<--If the textbox txtFirstName contains no data THEN do nothing
strWhere = strWhere & " (tblmain.incidenttype) Like '*" & Me.Text147 & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If
'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.Text149) Then '<--If the textbox txtFirstName contains no data THEN do nothing
strWhere = strWhere & " (tblmain.DirectSurveillence) Like '*" & Me.Text149 & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If
'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.Text182) Then '<--If the textbox txtFirstName contains no data THEN do nothing
strWhere = strWhere & " (tblmain.policemonitor) Like '*" & Me.Text182 & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If
'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.Text236) Then '<--If the textbox txtFirstName contains no data THEN do nothing
strWhere = strWhere & " (tblmain.policeresponce) Like '*" & Me.Text236 & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If
'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.Text256) Then '<--If the textbox txtFirstName contains no data THEN do nothing
strWhere = strWhere & " (tblmain.reason) Like '*" & Me.Text256 & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If
'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.Text267) Then '<--If the textbox txtFirstName contains no data THEN do nothing
strWhere = strWhere & " (tblmain.reportedby) Like '*" & Me.Text267 & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If
'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.Text264) Then '<--If the textbox txtFirstName contains no data THEN do nothing
strWhere = strWhere & " (tblmain.cadnum) Like '*" & Me.Text264 & "*' AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If
If Me.startdate <> "" And Me.enddate <> "" Then
strWhere = strWhere & " (tblmain.Datee) BETWEEN #" & Me.startdate & "# AND #" & Me.enddate & "# AND"
End If
'If Me.Text194 <> "" And Me.Text195 <> "" Then
'strWhere = strWhere & " (clients.returndate) BETWEEN #" & Me.Text194 & "# AND #" & Me.Text195 & "# AND"
'End If
'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)
'Pass the SQL to the RowSource of the listbox
Me.lstCustInfo.RowSource = strSql & " " & strWhere & "" & strOrder
Me.lstCustInfo.Requery
DoCmd.Requery
Me.Filter = strWhere
Me.FilterOn = True
End Sub
