I have this coding. The form the user has let's them select a date range & a status of Open or Closed. I think the codings right but no matter what date or user selects it brings up everything for whichever status was chosen. Once the code is run I look into the query & there is only 1 field(Status) & the criteria is Either Open or Closed depending on what was closed in the form. Everything else is blank
Code:
Private Sub OK_Click()
' Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String
' Get the database and stored query
Set db = CurrentDb()
Set qdf = db.QueryDefs("Range")
' Loop through the selected items in the list box and build a text string
If Me!Status1.ItemsSelected.Count > 0 Then
For Each varItem In Me!Status1.ItemsSelected
strCriteria = strCriteria & "[Vendor Hotline Log].Status = " & Chr(34) _
& Me!Status1.ItemData(varItem) & Chr(34) & " OR "
Next varItem
strCriteria = Left(strCriteria, Len(strCriteria) - 3)
End If
' Build the new SQL statement incorporating the string
strSQL = "SELECT * FROM [Vendor Hotline Log] " & _
"WHERE " & strCriteria & ";"
' Apply the new SQL statement to the query
Debug.Print strSQL
qdf.SQL = strSQL
' Open the query
DoCmd.OpenReport "Date Range", acViewPreview
DoCmd.Close acForm, "Search Criteria Form", acSaveNo
End Sub
Last edited:
