Filtering Multiple Values

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


"AccessVandal via AccessMonster.com" wrote:

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.

Melissa wrote:
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

--
Please Rate the posting if helps you

Message posted via http://www.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 wrote:
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.

--
Please Rate the posting if helps you

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200810/1

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


"AccessVandal via AccessMonster.com" wrote:

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 wrote:
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.

--
Please Rate the posting if helps you

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200810/1

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

Melissa wrote:
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

--
Please Rate the posting if helps you

Message posted via http://www.accessmonster.com

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


"AccessVandal via AccessMonster.com" wrote:

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

Melissa wrote:
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

--
Please Rate the posting if helps you

Message posted via http://www.accessmonster.com

To view the immediate window, in the VB editor, press Ctrl + G. Then copy and
paste the string here for us to see.

The code given by crystal. I’m not sure why she gave you that. In my opinion,
this will cause errors. Furthermore, it’s missing a parenthesis, quotes, and
incorrect use of “IN� clause. Not a complete code.

'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

In the command button “cmdFilter�, what was or is the string value before the
click event? If there was nothing, why the “ AND “ ? this can cause error.
And if there is nothing in the beginning on the “varFilter� then, remove the
“ AND �.

If you had multiple shipto addresses, you’ll need to open a recordset from
the filtered to correct company ID with multiple ShipTo addresses.

Is it possible not to filter the form with ship to?


Melissa wrote:
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

--
Please Rate the posting if helps you

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200810/1