I am supplying the code below, so if you can help me figure out where to put the piece of code that will get me the results I need, that would be so awesome!!
Code:
Dim dbNm As DatabaseDim qryDef As QueryDef
Set dbNm = CurrentDb()
'Constant Select statement for the Query definition
'strSQL = "SELECT tbl_weeklyMAs.ma_id,"
'strSQL = strSQL & "tbl_weeklyMAs.impacts,
tbl_weeklyMAs.submit_date, tbl_weeklyMAs.submit_date_copy, tbl_weeklyMAs.status, tbl_weeklyMAs.title, tbl_weeklyMAs.planned_start_date, tbl_weeklyMAs.planned_start_date_copy, tbl_weeklyMAs.ma_hyperlink, tbl_weeklyMAs.planned_end_date, tbl_weeklyMAs.planned_end_date_copy,"
'strSQL = strSQL & "tbl_weeklyMAs.requestor "
'
strSQL = "SELECT tbl_weeklyMAs.* "
strSQL = strSQL & "FROM tbl_weeklyMAs "
strWhere = "WHERE"
'strOrder = "ORDER BY tbl_weeklyMAs.ma_id;"'
'Set the WHERE clause for the QueryDef if information has been entered into a field on the form
If Not IsNull(Me.cboFilterImpacts) And
Me.cboFilterImpacts <> "" Then '<--If the textbox ma_id and the other text boxes contains no data THEN do nothing
strWhere = strWhere & " (tbl_weeklyMAs.impacts) Like '*" & Me.cboFilterImpacts & "*' AND" '<--otherwise, apply the LIKE statement to the QueryDef
End If
If Not IsNull(Me.txtFilter_ma_id) And
Me.txtFilter_ma_id <> "" Then
strWhere = strWhere & " (tbl_weeklyMAs.ma_id) Like '*" & Me.txtFilter_ma_id & "*' AND"
End If
If Not IsNull(Me.txtFilterSubmitDate) Then
strWhere = strWhere & " (tbl_weeklyMAs.submit_date) >= #" & Me.txtFilterSubmitDate & "# AND"
End If
If Not IsNull(Me.txtFilterSubmitDateEnd) Then
strWhere = strWhere & " (tbl_weeklyMAs.submit_date) <= #" & Me.txtFilterSubmitDateEnd & "# AND"
End If
If Not IsNull(Me.cboFilterStatus) And Me.cboFilterStatus <> "" Then
strWhere = strWhere & " (tbl_weeklyMAs.status) = '" &
Me.cboFilterStatus & "' AND"
End If
If Not IsNull(Me.txtFilterTitle) And Me.txtFilterTitle <> "" Then
strWhere = strWhere & " (tbl_weeklyMAs.title) Like '*" &
Me.txtFilterTitle & "*' AND"
End If
If Not IsNull(Me.txtFilterRequestor) And Me.txtFilterRequestor <> "" Then
strWhere = strWhere & " (tbl_weeklyMAs.requestor) Like '*" &
Me.txtFilterRequestor & "*' AND"
End If
If Not IsNull(Me.txtFilterPlannedStartDate) And Me.txtFilterPlannedStartDate <> "" Then
strWhere = strWhere & " (tbl_weeklyMAs.planned_start_date) >= #" & Me.txtFilterPlannedStartDate & "# AND"
End If
If Not IsNull(Me.txtFilterPlannedEndDate) Then
strWhere = strWhere & " (tbl_weeklyMAs.planned_end_date) <= #" & Me.txtFilterPlannedEndDate & "# AND"
End If
'Remove the last AND from the SQL statement
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)
'MsgBox strSQL & " " & strWhere & " " & strOrder
'Pass the QueryDef to the query
Set qryDef = dbNm.QueryDefs("qry_Search")
qryDef.SQL = strSQL & " " & strWhere & " " & strOrder
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim rsCnt As Integer 'the counter
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("qry_Search", dbOpenDynaset)
With rst
If .recordcount > 0 Then 'What you want done
DoCmd.OpenForm "frm_SearchResults"
Else
MsgBox "There is no data that meets your criteria. Please try again.", fbOKOnly, "No Data Found"
'What you want done
DoCmd.OpenForm "frm_search_MAs"
End If
End With
Set dbs = Nothing
Set rst = Nothing
Me.cboFilterImpacts = Null
Me.txtFilter_ma_id = Null
Me.txtFilterSubmitDate = Null
Me.txtFilterSubmitDateEnd = Null
Me.cboFilterStatus = Null
Me.txtFilterTitle = Null
Me.txtFilterPlannedStartDate = Null
Me.txtFilterPlannedEndDate = Null
Me.txtFilterRequestor = Null
ExitHandler:
Exit Sub
ErrorHandler:
If Err = 2489 Then
Resume ExitHandler
Else
MsgBox Err.Description
Resume ExitHandler
End If
Debug.Print strWhere
End Sub