Filtering Multiple Values

[Melissa]

Same thing.

It seems to have a problem with this code:

Me.Filter = varFilter

When I go into the debug window this is the code that is highlighted.
--
Melissa

Your original string was..

"([CSR] Like " '*" & Me.txtFilterCSR & "*')"

try to insert one ampersand like..

"([CSR] Like " & "'*" & Me.txtFilterCSR & "*')"

look at the single quote and double quotes.

I removed the parenthesis but it gives me the same message. Sometimes it
comes up with this error:

Syntax error (missing operator) in query expression ' AND ([CSR] = 'smit'

and sometimes it comes up with this one:

You can't assign a value to this object

If Not IsNull(Me.txtFilterCSR) Then
varFilter = (varFilter + " AND ") _
& "[CSR] = '" & Me.txtFilterCSR & "'"
End If

The plus should work, if it doesn't, change it to an ampersand.
If Not IsNull(Me.txtFilterCSR) Then
varFilter = (varFilter & " AND ") _
& "[CSR] = '" & Me.txtFilterCSR & "'"
End If

 

[AccessVandal via AccessMonster.com]

Key in this Debug.Print and look at your SQL string in the immediate window,
copy and paste the string to show us the results. Is the syntax correct?

If Not IsNull(Me.txtFilterCSR) Then
Debug.Print varFilter ' one before and....
varFilter = varFilter & " AND " _
& "[CSR] = '" & Me.txtFilterCSR & "'"
Debug.Print varFilter ' one after to view your strings
End If

 

[Melissa]

Here's where it takes me to in the SQL code when I go to the debug window

Me.Filter = varFilter
Me.FilterOn = True
Else
'show all records -- no filters specified
Me.FilterOn = False
End If

Me.Requery




--
Melissa

Key in this Debug.Print and look at your SQL string in the immediate window,
copy and paste the string to show us the results. Is the syntax correct?

If Not IsNull(Me.txtFilterCSR) Then
Debug.Print varFilter ' one before and....
varFilter = varFilter & " AND " _
& "[CSR] = '" & Me.txtFilterCSR & "'"
Debug.Print varFilter ' one after to view your strings
End If

Same thing.

It seems to have a problem with this code:

Me.Filter = varFilter

When I go into the debug window this is the code that is highlighted.

 

[AccessVandal via AccessMonster.com]

But what is the filter string? Is the syntax correct? Post the filtered
string here for us to see.

 

[Melissa]

My apologies... I am not too familiar with SQL terminology. I am learning
more and more as I read through these posts but any extra explanation you can
provide to me would be much appreciated.

Here is the filter string. I have several criteria setup to search from. But
for the purposes of this post, I am only including one of each type.
_______________________________________________________________________

Private Sub cmdFilter_Click()

Dim varFilter As Variant

'if the first control for ShipTo is filled out
If Not IsNull(Me.txtFilterShipTo) Then

'if it has multiple values
If InStr((Me.txtFilterShipTo), "~") > 0 Then
varFilter = (varFilter + " AND ") _
& "('~' & [ShipTo] & '~' in '" & Me.txtFilterShipTo & "'"
Else

'only the one value in ShipTo filter is filled out
varFilter = (varFilter + " AND ") _
& "[ShipTo]= '" & Me.txtFilterShipTo & "'"

End If
End If


If Not IsNull(Me.txtFilterState) Then
Debug.Print varFilter ' one before and....
varFilter = varFilter & " AND " _
& "[State] = '" & Me.txtFilterState & "'"
Debug.Print varFilter ' one after to view your strings
End If

If Not IsNull(Me.txtFilterEmail) Then
varFilter = (varFilter + " AND ") _
& "([EmailAddress] Like '*" _
& Me.txtFilterEmail & "*')"
End If


If Not IsNull(varFilter) Then

'remove next line after everything works ok
Debug.Print varWhere
'press CTRL-G to look at Immediate (debug) window

Me.Filter = varFilter
Me.FilterOn = True
Else
'show all records -- no filters specified
Me.FilterOn = False
End If

Me.Requery

End Sub
___________________________________________________________________


Thanks,
Melissa