Search form with results in subform view--Problems with code!

[gsimmons77]

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

[Rainlover]

I think but not 100% sure.

strFilter = Left$(strFilter, Len(strFilter) - 97) & ") And "

Should be

strFilter = Left$(strFilter, Len(strFilter) - 97)) & " And "

[gsimmons77]

I think but not 100% sure.

strFilter = Left$(strFilter, Len(strFilter) - 97) & ") And "

Should be

strFilter = Left$(strFilter, Len(strFilter) - 97)) & " And "

Hey Rainlover,

Thank you so much for the response! I tried the recommendation, and unfortunately, it didn't work. Any ideas?
Greg

[Rawb]

Have you tried removing that "$" from the function name?

[Rainlover]

Sorry about that.

You do not have matching opening and closing brackets.

Try

strFilter = Left$(strFilter, Len(strFilter) - 97) & " And "