Hello--------
I am trying to create a search form in Access. I have the following “unbound” fields:
Project Number as a text box
ARRA Number as a text box
State Number as a text box
County as a multi-select box with 96 possible selections
Region as a multi-select box with 4 possible selections
Project Type as a multi-select box with 24 possible selections
Everything is set to text. I’ve seen this work with bound and unbound fields. Since this is unbound, I’ve told Access where I want to pull from. You’ll see that in the code.
Here’s where the code is breaking. I am receiving the following error: Run time error 5, Invalid Procedure call or argument.
The following is the procedure with the error in RED-------
'If County
If Me!County.ItemsSelected.Count > 0 Then
strFilter = strFilter & "("
For Each varItem In Me!County.ItemsSelected
strFilter = strFilter & "qryMetrics.[County Name]=" & Chr(34) & Me!County.ItemData(varItem) & Chr(34) & " OR "
Next
strFilter = Left$(strFilter, Len(strFilter) - 97) & ") And "
End If
'If Region
If Me!Region.ItemsSelected.Count > 0 Then
strFilter = strFilter & "("
For Each varItem In Me!Region.ItemsSelected
strFilter = strFilter & "qryMetrics.[Region]=" & Chr(34) & Me!Region.ItemData(varItem) & Chr(34) & " OR "
Next
strFilter = Left$(strFilter, Len(strFilter) - 5) & ") AND "
End If
'If Project Purpose
If Me!ProjectPurpose.ItemsSelected.Count > 0 Then
strFilter = strFilter & "("
For Each varItem In Me!ProjectPurpose.ItemsSelected
strFilter = strFilter & "qryMetrics.[Project Purpose]=" & Chr(34) & Me!ProjectPurpose.ItemData(varItem) & Chr(34) & " OR "
Next
strFilter = Left$(strFilter, Len(strFilter) - 25) & ") AND "
End If
'If Status
If Me!Status.ItemsSelected.Count > 0 Then
strFilter = strFilter & "("
For Each varItem In Me!Status.ItemsSelected
strFilter = strFilter & "qryMetrics.[Status]=" & Chr(34) & Me!Status.ItemData(varItem) & Chr(34) & " OR "
Next
strFilter = Left$(strFilter, Len(strFilter) - 4) & ") AND "
End If
'If Employees
If Me!Employees.ItemsSelected.Count > 0 Then
strFilter = strFilter & "("
For Each varItem In Me!Employees.ItemsSelected
strFilter = strFilter & "qryMetrics.[EmployeeID]=" & Chr(34) & Me!Employees.ItemData(varItem) & Chr(34) & " OR "
Next
strFilter = Left$(strFilter, Len(strFilter) - 30) & ") AND "
End If
If strFilter <> "" Then
strFilter = Left$(strFilter, Len(strFilter) - 10)
End If
If strFilter = "" Then
Me!sfrmSearch.Form.FilterOn = False
Else
Me!sfrmSearch.Form.Filter = strFilter
Me!sfrmSearch.Form.FilterOn = True
End If
End Sub
__________________________________________________ ______________________
Function GetDateFilter(dtDate As Date) As String
' Date filters must be in MM/DD/YYYY format
GetDateFilter = "#" & Format(dtDate, "MM/DD/YYYY hh:mm:ss AM/PM") & "#"
End Function
I have been racking my brain on this one. Thank you to anyone who can assist!!!!!!
GSimmons