Private Sub btnSearch_Click()
Dim dbf As Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strWhere As String
strWhere = "WHERE "
strSQL = "SELECT tblCases.CaseNum, tblCases.OldCaseNum, tblNames.FirstName, tblNames.LastName,tblNames.Company , tblCases.OpenDate, tblCases.CloseDate, tblTracers.TracerNum, tblNames.TIN FROM (tblCases LEFT JOIN tblNames ON tblCases.CaseNum = tblNames.CaseNum) LEFT JOIN tblTracers ON tblCases.CaseNum = tblTracers.CaseNum "
If Not IsNull(Me.txtCaseNum) Then
strWhere = strWhere & "tblCases.CaseNum LIKE '*" & Me.txtCaseNum & "*'"
End If
'----------
If Not IsNull(Me.txtFirstName) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblNames.FirstName LIKE '*" & Me.txtFirstName & "*'"
End If
'----------
If Not IsNull(Me.txtLastName) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblNames.LastName LIKE '*" & Me.txtLastName & "*'"
End If
'----------
If Not IsNull(Me.txtTIN) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblNames.TIN LIKE '*" & Me.txtTIN & "*'"
End If
'----------
If Not IsNull(Me.txtOldCaseNum) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblCases.OldCaseNum LIKE '*" & Me.txtOldCaseNum & "*'"
End If
'----------
If Not IsNull(Me.txtCompany) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblNames.Company LIKE '*" & Me.txtCompany & "*'"
End If
'----------
If Not IsNull(Me.txtTracerNum) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblTracers.TracerNum LIKE '*" & Me.txtTracerNum & "*'"
End If
'----------
If IsDate(Me.txtOpenDateFrom) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblCases.OpenDate >= " & "#" & Me.txtOpenDateFrom & "#"
End If
'----------
If IsDate(Me.txtOpenedDateTo) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblCases.OpenDate <= " & "#" & Me.txtOpenedDateTo & "#"
End If
'----------
If IsDate(Me.txtCloseDateFrom) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblCases.CloseDate >= " & "#" & Me.txtCloseDateFrom & "#"
End If
'----------
If IsDate(Me.txtCloseDateTo) Then
If Len(strWhere) > 6 Then
strWhere = strWhere & " AND "
End If
strWhere = strWhere & "tblCases.CloseDate <= " & "#" & Me.txtCloseDateTo & "#"
End If
'----------
If Len(strWhere) < 8 Then
strWhere = ""
End If
strWhere = strWhere & " GROUP BY tblCases.CaseNum, tblCases.OldCaseNum, tblNames.FirstName, tblNames.LastName, tblNames.Company, tblCases.OpenDate, tblCases.CloseDate, tblTracers.TracerNum, tblNames.TIN"
strSQL = strSQL & strWhere & ";"
'2 Debug lines below, delete when working
Debug.Print strWhere
Debug.Print strSQL
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.sbfBrowse.Form.Filter = Mid(strWhere, 7)
Me.sbfBrowse.Form.FilterOn = True
'Set dbf = CurrentDb
'Set rst = dbf.OpenRecordset(strSQL)
End Sub