Filtering Multiple Values

[Melissa]

I have a search form with the following fields.

Text - Region
Text - Ship To
Text - State
Yes/No - Communication
Yes/No - Performance
Yes/No - Fuel

I would like to be able to enter multiple values in the fields. For example,
find results where the Ship To is 123456 or 111111. Is this possible?

Here is the code I am using. And again, let me preface this question with
the fact that I am not very familiar with VBA code. As much explanation as to
how to modify this code to work is appreciated.


If Not IsNull(Me.TxtFilterShipTo) Then
strWhere = strWhere & "([ShipTo] = """ & Me.TxtFilterShipTo & """)
AND "
End If


Thanks,
Melissa

 

[strive4peace]

Hi Melissa,

As much explanation as to

how to modify this code to work is appreciated.


If Not IsNull(Me.TxtFilterShipTo) Then
strWhere = strWhere & "([ShipTo] = """ & Me.TxtFilterShipTo & """) AND "
End If

this:
If Not IsNull(Me.TxtFilterShipTo)
checks to see if you have filled out the TxtFilterShipTo control.

this:
strWhere = strWhere & "([ShipTo] = """ & Me.TxtFilterShipTo & """) AND "

adds the ShipTo file to the filter string. If you have "Mary Smith" in
the TxtFilterShipTo control, this is the result of what is built

strWhere = strWhere & "([ShipTo] = ""Mary Smith"") AND "

if you want a double quote in the middle of a string that is delimited
with double quotes, you need to use 2 of them

personally, I do not like automatically concatenating the 'AND' onto the
end -- because then, your code has to remove it when you are done. It
is better to take advantage of using a variant for the filter and using
+ instead of & to concatenate the AND

read about the Difference between & and + here:

Access Basics by Crystal
http://www.accessmvp.com/strive4peace
8-part free tutorial that covers essentials in Access

If you want to be able to choose more than one control to filter the
ShipTo field, the easiest way is to add another textbox to your form header

if you have 2 textboxes to choose for the ShipTo filed and they are named
TxtFilterShipTo1
TxtFilterShipTo2

then, you would want to use an OR condition ... but the second one might
not be filled out, so you would do this:

'~~~~~~~~~~~~~~~~~~~
dim varFilter as variant
varFilter = null

'if the first filter control for ShipTo is filled out
If not IsNull(me.TxtFilterShipTo1 ) Then

'if the second filter control for ShipTo is also filled out
If not IsNull(me.TxtFilterShipTo2 ) Then
varFilter = (varFilter + " AND " ) _
& "([ShipTo]= '" & me.TxtFilterShipTo1 & "'" _
& " OR [ShipTo]= '" & me.TxtFilterShipTo1 & "')" _

Else

'only the first ShipTo filter is filled out
varFilter = (varFilter + " AND " ) _
& "[ShipTo]= '" & me.TxtFilterShipTo1 & "'"

End If
end if
'~~~~~~~~~~~~~~~~~~~

space underscore at the end of a line means that the statement is
continued on the next line

here is some generic information on building filter strings:

Set Form or Subform Filter
---

Put comboboxes and textboxes on the form (I put then in the header and
give them a unique background color so they are not confused with data
controls). Assign this to the AfterUpdate event of each one...

=SetFormFilter()

then put this code behind the form

'~~~~~~~~~~~~~~~
Private Function SetFormFilter()

dim varFilter as variant
varFilter = null

If not IsNull(me.text_controlname ) Then
varFilter = (varFilter + " AND " ) _
& "[TextFieldname]= '" & me.text_controlname & "'"
end if

If not IsNull(me.date_controlname ) Then
varFilter = (varFilter + " AND " ) _
& "[DateFieldname]= #" & me.date_controlname & "#"
end if

If not IsNull(me.numeric_controlname ) Then
varFilter = (varFilter + " AND " ) _
& me.numeric_controlname
end if

'**************************************************
' Choose on of following code blocks
'**************************************************

'--------------- Filter form you are behind
if not IsNull(varFilter) then
me.filter = varFilter
me.FilterOn = true
else
me.FilterOn = false
end if

me.requery


'OR

'--------------- Filter subform
' if not IsNull(varFilter) then
' me.subformcontrolname.form.filter = varFilter
' me.subformcontrolname.form.FilterOn = true
' else
' me.subformcontrolname.form.FilterOn = false
' end if

' me.subformcontrolname.form.requery

End Function
'~~~~~~~~~~~~~~~



me.numeric_controlname refers to the NAME property of a control on the
form you are behind
(Me. represents the form -- kinda like "me" for me is not "me" for you )

delimiters are used for data that is not a number
quote marks ' or " for text
number signs # for dates

varFilter is a variant that will hold the string you are building for
each condition
-- but if nothing is specified in the filter control (IsNull),
then that addition to the filter string is skipped.

finally, when the filter string is done, it is applied to your form.

That means that as you flip through records, ONLY records matching that
filter will show

Then, put another command button on the form

Name --> btnShowAll
OnClick --> [Event Procedure]

'~~~~~~~~~~~~~~~
me.filteron = false
me.requery
'~~~~~~~~~~~~~~~


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

I have a search form with the following fields.

Text - Region
Text - Ship To
Text - State
Yes/No - Communication
Yes/No - Performance
Yes/No - Fuel

I would like to be able to enter multiple values in the fields. For example,
find results where the Ship To is 123456 or 111111. Is this possible?

Here is the code I am using. And again, let me preface this question with
the fact that I am not very familiar with VBA code. As much explanation as to
how to modify this code to work is appreciated.


If Not IsNull(Me.TxtFilterShipTo) Then
strWhere = strWhere & "([ShipTo] = """ & Me.TxtFilterShipTo & """)
AND "
End If


Thanks,
Melissa

 

[Melissa]

Hi Crystal,

Thank you for the in depth explanation. Unfortunately, adding additional
text boxes to filter additional values will not work for this scenario. I
don't want to restrict the amount of values that can be searched as the
scenarios are not always the same. Is there a way to search multiple values
in one text field?

Best Regards,
--
Melissa

Hi Melissa,

As much explanation as to

how to modify this code to work is appreciated.


If Not IsNull(Me.TxtFilterShipTo) Then
strWhere = strWhere & "([ShipTo] = """ & Me.TxtFilterShipTo & """) AND "
End If

this:
If Not IsNull(Me.TxtFilterShipTo)
checks to see if you have filled out the TxtFilterShipTo control.

this:
strWhere = strWhere & "([ShipTo] = """ & Me.TxtFilterShipTo & """) AND "

adds the ShipTo file to the filter string. If you have "Mary Smith" in
the TxtFilterShipTo control, this is the result of what is built

strWhere = strWhere & "([ShipTo] = ""Mary Smith"") AND "

if you want a double quote in the middle of a string that is delimited
with double quotes, you need to use 2 of them

personally, I do not like automatically concatenating the 'AND' onto the
end -- because then, your code has to remove it when you are done. It
is better to take advantage of using a variant for the filter and using
+ instead of & to concatenate the AND

read about the Difference between & and + here:

Access Basics by Crystal
http://www.accessmvp.com/strive4peace
8-part free tutorial that covers essentials in Access

If you want to be able to choose more than one control to filter the
ShipTo field, the easiest way is to add another textbox to your form header

if you have 2 textboxes to choose for the ShipTo filed and they are named
TxtFilterShipTo1
TxtFilterShipTo2

then, you would want to use an OR condition ... but the second one might
not be filled out, so you would do this:

'~~~~~~~~~~~~~~~~~~~
dim varFilter as variant
varFilter = null

'if the first filter control for ShipTo is filled out
If not IsNull(me.TxtFilterShipTo1 ) Then

'if the second filter control for ShipTo is also filled out
If not IsNull(me.TxtFilterShipTo2 ) Then
varFilter = (varFilter + " AND " ) _
& "([ShipTo]= '" & me.TxtFilterShipTo1 & "'" _
& " OR [ShipTo]= '" & me.TxtFilterShipTo1 & "')" _

Else

'only the first ShipTo filter is filled out
varFilter = (varFilter + " AND " ) _
& "[ShipTo]= '" & me.TxtFilterShipTo1 & "'"

End If
end if
'~~~~~~~~~~~~~~~~~~~

space underscore at the end of a line means that the statement is
continued on the next line

here is some generic information on building filter strings:

Set Form or Subform Filter
---

Put comboboxes and textboxes on the form (I put then in the header and
give them a unique background color so they are not confused with data
controls). Assign this to the AfterUpdate event of each one...

=SetFormFilter()

then put this code behind the form

'~~~~~~~~~~~~~~~
Private Function SetFormFilter()

dim varFilter as variant
varFilter = null

If not IsNull(me.text_controlname ) Then
varFilter = (varFilter + " AND " ) _
& "[TextFieldname]= '" & me.text_controlname & "'"
end if

If not IsNull(me.date_controlname ) Then
varFilter = (varFilter + " AND " ) _
& "[DateFieldname]= #" & me.date_controlname & "#"
end if

If not IsNull(me.numeric_controlname ) Then
varFilter = (varFilter + " AND " ) _
& me.numeric_controlname
end if

'**************************************************
' Choose on of following code blocks
'**************************************************

'--------------- Filter form you are behind
if not IsNull(varFilter) then
me.filter = varFilter
me.FilterOn = true
else
me.FilterOn = false
end if

me.requery


'OR

'--------------- Filter subform
' if not IsNull(varFilter) then
' me.subformcontrolname.form.filter = varFilter
' me.subformcontrolname.form.FilterOn = true
' else
' me.subformcontrolname.form.FilterOn = false
' end if

' me.subformcontrolname.form.requery

End Function
'~~~~~~~~~~~~~~~



me.numeric_controlname refers to the NAME property of a control on the
form you are behind
(Me. represents the form -- kinda like "me" for me is not "me" for you )

delimiters are used for data that is not a number
quote marks ' or " for text
number signs # for dates

varFilter is a variant that will hold the string you are building for
each condition
-- but if nothing is specified in the filter control (IsNull),
then that addition to the filter string is skipped.

finally, when the filter string is done, it is applied to your form.

That means that as you flip through records, ONLY records matching that
filter will show

Then, put another command button on the form

Name --> btnShowAll
OnClick --> [Event Procedure]

'~~~~~~~~~~~~~~~
me.filteron = false
me.requery
'~~~~~~~~~~~~~~~


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

I have a search form with the following fields.

Text - Region
Text - Ship To
Text - State
Yes/No - Communication
Yes/No - Performance
Yes/No - Fuel

I would like to be able to enter multiple values in the fields. For example,
find results where the Ship To is 123456 or 111111. Is this possible?

Here is the code I am using. And again, let me preface this question with
the fact that I am not very familiar with VBA code. As much explanation as to
how to modify this code to work is appreciated.


If Not IsNull(Me.TxtFilterShipTo) Then
strWhere = strWhere & "([ShipTo] = """ & Me.TxtFilterShipTo & """)
AND "
End If


Thanks,
Melissa

 

[strive4peace]

Hi Melissa,

yes -- but it requires more complex coding

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Melissa]

Crystal,

Any assistance you can provide on this would be appreciated.

 

[strive4peace]

Hi Melissa,

please tell me the lines that were confusing in what I gave you before.
It is necessary that you thoroughly understand what has been provided
before I give you something more complex


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Melissa]

I can read and understand the code enough to manipulate it as needed. Where I
have problems, is when I have to write it from scratch. I am not familiar
enough with it to know where to place all the symbols (i.e. quotes,
ampersands, parenthesis...)

I am not sure if it's possible to send me some sample code that I could then
customize but that is the easiest for me to work with.

Thanks,
Melissa

 

[strive4peace]

Hi Melissa,

assuming your "find" textbox will contain text values... this is not a
perfect way, but is easiest...

if the user will [ut in multiple values, delimit each value with
something that is not contained in any of your values, like tilde ~

ie:
~value1~value2~

do not put any extra spaces in there -- just exactly what you are
looking for between each set of tildes

then, in the code to evaluate it:

'~~~~~~~~~~~~~~~~~~~
dim varFilter as variant
varFilter = null

'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.TxtFilterShipTo1 & "'"
Else

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

End If
end if
'~~~~~~~~~~~~~~~~~~~



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Melissa]

Crystal,

I know this code is holding it up from working because the filter is not a
string but I am not sure how to adjust it. Any ideas?

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True

End If
End Sub

_______________________________________________________________________

Here's the whole thing ---

Private Sub cmdFilter_Click()

If Not IsNull(Me.txtFilterRegion) Then
strWhere = strWhere & "([Region] Like ""*" & Me.txtFilterRegion &
"*"") AND "
End If

If Not IsNull(Me.txtFilterState) Then
strWhere = strWhere & "([MarketState] = """ & Me.txtFilterState &
""") AND "
End If


Dim varFilter As Variant
varFilter = Null

'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.txtFilterEmail) Then
strWhere = strWhere & "([EmailAddress] Like ""*" & Me.txtFilterEmail
& "*"") AND "
End If

If Not IsNull(Me.txtFilterCategory) Then
strWhere = strWhere & "([Category] = """ & Me.txtFilterCategory &
""") AND "
End If

If Not IsNull(Me.txtFilterCSR) Then
strWhere = strWhere & "([CSR] Like ""*" & Me.txtFilterCSR & "*"")
AND "
End If


If Me.cboFilterDistComm = "All" Then
'do nothing
Else
strWhere = strWhere _
& "([Distributor Communications] = " &
Me.cboFilterDistComm & ") AND "
End If

If Me.cboFilterPerfScore = "All" Then
'do nothing
Else
strWhere = strWhere _
& "([Performance Scorecard] = " & Me.cboFilterPerfScore &
") AND "
End If

If Me.cboFilterFuel = "All" Then
'do nothing
Else
strWhere = strWhere _
& "([Fuel Surcharge] = " & Me.cboFilterFuel & ") AND "
End If


lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True

End If
End Sub
--
Melissa

Hi Melissa,

assuming your "find" textbox will contain text values... this is not a
perfect way, but is easiest...

if the user will [ut in multiple values, delimit each value with
something that is not contained in any of your values, like tilde ~

ie:
~value1~value2~

do not put any extra spaces in there -- just exactly what you are
looking for between each set of tildes

then, in the code to evaluate it:

'~~~~~~~~~~~~~~~~~~~
dim varFilter as variant
varFilter = null

'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.TxtFilterShipTo1 & "'"
Else

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

End If
end if
'~~~~~~~~~~~~~~~~~~~



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

I can read and understand the code enough to manipulate it as needed. Where I
have problems, is when I have to write it from scratch. I am not familiar
enough with it to know where to place all the symbols (i.e. quotes,
ampersands, parenthesis...)

I am not sure if it's possible to send me some sample code that I could then
customize but that is the easiest for me to work with.

Thanks,
Melissa

 

[strive4peace]

Hi Melissa,

before
Me.Filter = strWhere

add this line:
debug.print strWhere

I am actually using
varWhere
not strWhere

press CTRL-G to examine what went to the Immediate (debug) window and
paste it into a message

I have tried to correct the code you posted -- but I do not have your
database to test it with so chances are, there will be things you need
to modify

I also added an error handler to your code

'~~~~~~~~~~~~~~~~~~~~~~~~~~

Private Sub cmdFilter_Click()

On Error GoTo Proc_Err

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.txtFilterEmail) Then
varFilter = (varFilter + " AND ") _
& "([EmailAddress] Like '*" _
& Me.txtFilterEmail & "*')"
End If

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

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

If Not IsNull(Me.cboFilterDistComm) Then
If Me.cboFilterDistComm = "All" Then
'do nothing
Else
varFilter = (varFilter + " AND ") _
& "([Distributor Communications] = '" _
& Me.cboFilterDistComm & "')"
End If

If Not IsNull(Me.cboFilterPerfScore) Then
If Me.cboFilterPerfScore = "All" Then
'do nothing
Else
'if this is text, you need to delimit the value
varFilter = (varFilter + " AND ") _
& "([Performance Scorecard] = " _
& Me.cboFilterPerfScore & ")"
End If
End If

If Not IsNull(Me.cboFilterFuel) Then
If Me.cboFilterFuel = "All" Then
'do nothing
Else
'if this is text, you need to delimit the value
varFilter = (varFilter + " AND ") _
& "([Fuel Surcharge] = " _
& Me.cboFilterFuel & ")"
End If
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


Proc_Exit:
Exit sub

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " cmdFilter_Click"

Resume Proc_Exit

'if you want to single-step code to find error, CTRL-Break at MsgBox
'then set this to be the next statement
Resume

End Sub

'~~~~~~~~~~~~~~~~~~~~~~~~

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code, references, or switch versions, you should
always compile before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~
if you run code without compiling it, you risk corrupting your database



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Crystal,

I know this code is holding it up from working because the filter is not a
string but I am not sure how to adjust it. Any ideas?

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True

End If
End Sub

_______________________________________________________________________

Here's the whole thing ---

Private Sub cmdFilter_Click()

If Not IsNull(Me.txtFilterRegion) Then
strWhere = strWhere & "([Region] Like ""*" & Me.txtFilterRegion &
"*"") AND "
End If

If Not IsNull(Me.txtFilterState) Then
strWhere = strWhere & "([MarketState] = """ & Me.txtFilterState &
""") AND "
End If


Dim varFilter As Variant
varFilter = Null

'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.txtFilterEmail) Then
strWhere = strWhere & "([EmailAddress] Like ""*" & Me.txtFilterEmail
& "*"") AND "
End If

If Not IsNull(Me.txtFilterCategory) Then
strWhere = strWhere & "([Category] = """ & Me.txtFilterCategory &
""") AND "
End If

If Not IsNull(Me.txtFilterCSR) Then
strWhere = strWhere & "([CSR] Like ""*" & Me.txtFilterCSR & "*"")
AND "
End If


If Me.cboFilterDistComm = "All" Then
'do nothing
Else
strWhere = strWhere _
& "([Distributor Communications] = " &
Me.cboFilterDistComm & ") AND "
End If

If Me.cboFilterPerfScore = "All" Then
'do nothing
Else
strWhere = strWhere _
& "([Performance Scorecard] = " & Me.cboFilterPerfScore &
") AND "
End If

If Me.cboFilterFuel = "All" Then
'do nothing
Else
strWhere = strWhere _
& "([Fuel Surcharge] = " & Me.cboFilterFuel & ") AND "
End If


lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True

End If
End Sub

 

[strive4peace]

see there is an End If missing here:

Else
varFilter = (varFilter + " AND ") _
& "([Distributor Communications] = '" _
& Me.cboFilterDistComm & "')"
END IF
End If


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Hi Melissa,

before
Me.Filter = strWhere

add this line:
debug.print strWhere

I am actually using
varWhere
not strWhere

press CTRL-G to examine what went to the Immediate (debug) window and
paste it into a message

I have tried to correct the code you posted -- but I do not have your
database to test it with so chances are, there will be things you need
to modify

I also added an error handler to your code

'~~~~~~~~~~~~~~~~~~~~~~~~~~

Private Sub cmdFilter_Click()

On Error GoTo Proc_Err

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.txtFilterEmail) Then
varFilter = (varFilter + " AND ") _
& "([EmailAddress] Like '*" _
& Me.txtFilterEmail & "*')"
End If

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

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

If Not IsNull(Me.cboFilterDistComm) Then
If Me.cboFilterDistComm = "All" Then
'do nothing
Else
varFilter = (varFilter + " AND ") _
& "([Distributor Communications] = '" _
& Me.cboFilterDistComm & "')"
End If

If Not IsNull(Me.cboFilterPerfScore) Then
If Me.cboFilterPerfScore = "All" Then
'do nothing
Else
'if this is text, you need to delimit the value
varFilter = (varFilter + " AND ") _
& "([Performance Scorecard] = " _
& Me.cboFilterPerfScore & ")"
End If
End If

If Not IsNull(Me.cboFilterFuel) Then
If Me.cboFilterFuel = "All" Then
'do nothing
Else
'if this is text, you need to delimit the value
varFilter = (varFilter + " AND ") _
& "([Fuel Surcharge] = " _
& Me.cboFilterFuel & ")"
End If
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


Proc_Exit:
Exit sub

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " cmdFilter_Click"

Resume Proc_Exit

'if you want to single-step code to find error, CTRL-Break at MsgBox
'then set this to be the next statement
Resume

End Sub

'~~~~~~~~~~~~~~~~~~~~~~~~

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code, references, or switch versions, you should
always compile before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~
if you run code without compiling it, you risk corrupting your database



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Crystal,

I know this code is holding it up from working because the filter is
not a string but I am not sure how to adjust it. Any ideas?
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

_______________________________________________________________________

Here's the whole thing ---

Private Sub cmdFilter_Click()

If Not IsNull(Me.txtFilterRegion) Then
strWhere = strWhere & "([Region] Like ""*" &
Me.txtFilterRegion & "*"") AND "
End If
If Not IsNull(Me.txtFilterState) Then
strWhere = strWhere & "([MarketState] = """ &
Me.txtFilterState & """) AND "
End If
Dim varFilter As Variant
varFilter = Null

'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.txtFilterEmail) Then
strWhere = strWhere & "([EmailAddress] Like ""*" &
Me.txtFilterEmail & "*"") AND "
End If
If Not IsNull(Me.txtFilterCategory) Then
strWhere = strWhere & "([Category] = """ &
Me.txtFilterCategory & """) AND "
End If
If Not IsNull(Me.txtFilterCSR) Then
strWhere = strWhere & "([CSR] Like ""*" & Me.txtFilterCSR &
"*"") AND "
End If

If Me.cboFilterDistComm = "All" Then
'do nothing
Else
strWhere = strWhere _
& "([Distributor Communications] = " &
Me.cboFilterDistComm & ") AND "
End If

If Me.cboFilterPerfScore = "All" Then
'do nothing
Else
strWhere = strWhere _
& "([Performance Scorecard] = " &
Me.cboFilterPerfScore & ") AND "
End If

If Me.cboFilterFuel = "All" Then
'do nothing
Else
strWhere = strWhere _
& "([Fuel Surcharge] = " & Me.cboFilterFuel & ") AND "
End If


lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

 

[Melissa]

Crystal,

When I run the filter it comes back with error "You can't assign a value to
this object." It does this for all of the filters.
--
Melissa

Hi Melissa,

before
Me.Filter = strWhere

add this line:
debug.print strWhere

I am actually using
varWhere
not strWhere

press CTRL-G to examine what went to the Immediate (debug) window and
paste it into a message

I have tried to correct the code you posted -- but I do not have your
database to test it with so chances are, there will be things you need
to modify

I also added an error handler to your code

'~~~~~~~~~~~~~~~~~~~~~~~~~~

Private Sub cmdFilter_Click()

On Error GoTo Proc_Err

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.txtFilterEmail) Then
varFilter = (varFilter + " AND ") _
& "([EmailAddress] Like '*" _
& Me.txtFilterEmail & "*')"
End If

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

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

If Not IsNull(Me.cboFilterDistComm) Then
If Me.cboFilterDistComm = "All" Then
'do nothing
Else
varFilter = (varFilter + " AND ") _
& "([Distributor Communications] = '" _
& Me.cboFilterDistComm & "')"
End If

If Not IsNull(Me.cboFilterPerfScore) Then
If Me.cboFilterPerfScore = "All" Then
'do nothing
Else
'if this is text, you need to delimit the value
varFilter = (varFilter + " AND ") _
& "([Performance Scorecard] = " _
& Me.cboFilterPerfScore & ")"
End If
End If

If Not IsNull(Me.cboFilterFuel) Then
If Me.cboFilterFuel = "All" Then
'do nothing
Else
'if this is text, you need to delimit the value
varFilter = (varFilter + " AND ") _
& "([Fuel Surcharge] = " _
& Me.cboFilterFuel & ")"
End If
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


Proc_Exit:
Exit sub

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " cmdFilter_Click"

Resume Proc_Exit

'if you want to single-step code to find error, CTRL-Break at MsgBox
'then set this to be the next statement
Resume

End Sub

'~~~~~~~~~~~~~~~~~~~~~~~~

'~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code, references, or switch versions, you should
always compile before executing.

from the menu in a VBE (module) window: Debug, Compile

fix any errors on the yellow highlighted lines

keep compiling until nothing happens (this is good!)

~~
if you run code without compiling it, you risk corrupting your database



Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

Crystal,

I know this code is holding it up from working because the filter is not a
string but I am not sure how to adjust it. Any ideas?

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True

End If
End Sub

_______________________________________________________________________

Here's the whole thing ---

Private Sub cmdFilter_Click()

If Not IsNull(Me.txtFilterRegion) Then
strWhere = strWhere & "([Region] Like ""*" & Me.txtFilterRegion &
"*"") AND "
End If

If Not IsNull(Me.txtFilterState) Then
strWhere = strWhere & "([MarketState] = """ & Me.txtFilterState &
""") AND "
End If


Dim varFilter As Variant
varFilter = Null

'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.txtFilterEmail) Then
strWhere = strWhere & "([EmailAddress] Like ""*" & Me.txtFilterEmail
& "*"") AND "
End If

If Not IsNull(Me.txtFilterCategory) Then
strWhere = strWhere & "([Category] = """ & Me.txtFilterCategory &
""") AND "
End If

If Not IsNull(Me.txtFilterCSR) Then
strWhere = strWhere & "([CSR] Like ""*" & Me.txtFilterCSR & "*"")
AND "
End If


If Me.cboFilterDistComm = "All" Then
'do nothing
Else
strWhere = strWhere _
& "([Distributor Communications] = " &
Me.cboFilterDistComm & ") AND "
End If

If Me.cboFilterPerfScore = "All" Then
'do nothing
Else
strWhere = strWhere _
& "([Performance Scorecard] = " & Me.cboFilterPerfScore &
") AND "
End If

If Me.cboFilterFuel = "All" Then
'do nothing
Else
strWhere = strWhere _
& "([Fuel Surcharge] = " & Me.cboFilterFuel & ") AND "
End If


lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True

End If
End Sub

 

[strive4peace]

Hi Melissa,

kinda hard to know what the problem is without seeing your database...
are your filter controls UNbound (no ControlSource)?


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Melissa]

Hi Crystal,

I really appreciate all of the help you are giving me with this...

Yes, all of my filter controls are unbound.

 

[strive4peace]

Hi Melissa,

Add an error handler to your code

put this at the top of your program, right after the procedure
declaration (skip a line first for better readability)

then come the statements of your procedure

then the lines at the bottom -- be sure to replace ProcedureName


'~~~~~~~~~~~~~~~~~~~~~~
'set up Error Handler
On Error GoTo Proc_Err
'~~~~~~~~~~~~~~~~~~~~~~

... then your statements


put this at the end of the procedure

'~~~~~~~~~~~~~~~~~~~~~~
Proc_Exit:
On Error Resume Next
'release object variables if any -- ie:
' if Not rst is Nothing then
' rst.close
' set rst = Nothing
' end if
Exit Sub 'or Exit Function

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " ProcedureName"

Resume Proc_Exit

'if you want to single-step code to find error, CTRL-Break at MsgBox
'then set this to be the next statement
Resume
'~~~~~~~~~~~~~~~~~~~~~~


where
ProcedureName is the name of your procedure so you can identify what
code the problem is in when you see the error

The line labels do not matter (Proc_Exit:, Proc_Err, I like to use the
same ones all the time -- they only have to be unique within a procedure.

if you get an error, press CTRL-BREAK when the message box pops up,
and possibly then Enter to dismiss the dialog box

this takes you into the code

right-click on the *Resume* statement
from the shortcut menu, choose --> Set Next Statement

then press F8 to resume with the statement that caused the problem --
you can fix it! -- or at least see what the problem is

pressing F8 executes one statement at a time

press F5 to continue execution automatically


~~~
While I am developing, I like to make the error handler go to the line
that caused the problem so I can see where it is. Resume goes back to
the offending line. When code Stops, press F8 to execute one statement
at a time.


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[Melissa]

Hi Crystal,

I added the error handler and here's what it does:

Error comes up "You can't assign a value to this object".
I select CTRL + BREAK and hit Enter.
This takes me to the code. I select Next Statement from the Shortcut menu
and press F8.
It takes me to the Proc_Exit code.
Press F8 again
It takes me to Exit Sub 'or Exit Function
Press F8 again
No other code is highlighted

I also want to mention that the error "You can't assign a value to this
object" comes up with all of the filter controls I have setup.

Here's the code:

Private Sub cmdFilter_Click()

'set up Error Handler
On Error GoTo Proc_Err

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.txtFilterEmail) Then
varFilter = (varFilter + " AND ") _
& "([EmailAddress] Like '*" _
& Me.txtFilterEmail & "*')"
End If

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

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

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

If Not IsNull(Me.cboFilterDistComm) Then
If Me.cboFilterDistComm = "All" Then
'do nothing
Else
varFilter = (varFilter + " AND ") _
& "([Distributor Communications] = '" _
& Me.cboFilterDistComm & "')"
End If

If Not IsNull(Me.cboFilterPerfScore) Then
If Me.cboFilterPerfScore = "All" Then
'do nothing
Else
'if this is text, you need to delimit the value
varFilter = (varFilter + " AND ") _
& "([Performance Scorecard] = " _
& Me.cboFilterPerfScore & ")"
End If
End If

If Not IsNull(Me.cboFilterFuel) Then
If Me.cboFilterFuel = "All" Then
'do nothing
Else
'if this is text, you need to delimit the value
varFilter = (varFilter + " AND ") _
& "([Fuel Surcharge] = " _
& Me.cboFilterFuel & ")"
End If
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


Proc_Exit:
On Error Resume Next
'release object variables if any -- ie:
' if Not rst is Nothing then
' rst.close
' set rst = Nothing
' end if
Exit Sub 'or Exit Function

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " cmdFilter_Click()"

Resume Proc_Exit


End If

End Sub

 

[Melissa]

Hi Crystal,

I haven't changed the code but now when I run the filter it gives me this
error:

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

In this example, I entered Smith in the CSR unbound text box.


--
Melissa

Hi Crystal,

I added the error handler and here's what it does:

Error comes up "You can't assign a value to this object".
I select CTRL + BREAK and hit Enter.
This takes me to the code. I select Next Statement from the Shortcut menu
and press F8.
It takes me to the Proc_Exit code.
Press F8 again
It takes me to Exit Sub 'or Exit Function
Press F8 again
No other code is highlighted

I also want to mention that the error "You can't assign a value to this
object" comes up with all of the filter controls I have setup.

Here's the code:

Private Sub cmdFilter_Click()

'set up Error Handler
On Error GoTo Proc_Err

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.txtFilterEmail) Then
varFilter = (varFilter + " AND ") _
& "([EmailAddress] Like '*" _
& Me.txtFilterEmail & "*')"
End If

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

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

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

If Not IsNull(Me.cboFilterDistComm) Then
If Me.cboFilterDistComm = "All" Then
'do nothing
Else
varFilter = (varFilter + " AND ") _
& "([Distributor Communications] = '" _
& Me.cboFilterDistComm & "')"
End If

If Not IsNull(Me.cboFilterPerfScore) Then
If Me.cboFilterPerfScore = "All" Then
'do nothing
Else
'if this is text, you need to delimit the value
varFilter = (varFilter + " AND ") _
& "([Performance Scorecard] = " _
& Me.cboFilterPerfScore & ")"
End If
End If

If Not IsNull(Me.cboFilterFuel) Then
If Me.cboFilterFuel = "All" Then
'do nothing
Else
'if this is text, you need to delimit the value
varFilter = (varFilter + " AND ") _
& "([Fuel Surcharge] = " _
& Me.cboFilterFuel & ")"
End If
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


Proc_Exit:
On Error Resume Next
'release object variables if any -- ie:
' if Not rst is Nothing then
' rst.close
' set rst = Nothing
' end if
Exit Sub 'or Exit Function

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " cmdFilter_Click()"

Resume Proc_Exit


End If

End Sub

--
Melissa

Hi Melissa,

Add an error handler to your code

put this at the top of your program, right after the procedure
declaration (skip a line first for better readability)

then come the statements of your procedure

then the lines at the bottom -- be sure to replace ProcedureName


'~~~~~~~~~~~~~~~~~~~~~~
'set up Error Handler
On Error GoTo Proc_Err
'~~~~~~~~~~~~~~~~~~~~~~

... then your statements


put this at the end of the procedure

'~~~~~~~~~~~~~~~~~~~~~~
Proc_Exit:
On Error Resume Next
'release object variables if any -- ie:
' if Not rst is Nothing then
' rst.close
' set rst = Nothing
' end if
Exit Sub 'or Exit Function

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " ProcedureName"

Resume Proc_Exit

'if you want to single-step code to find error, CTRL-Break at MsgBox
'then set this to be the next statement
Resume
'~~~~~~~~~~~~~~~~~~~~~~


where
ProcedureName is the name of your procedure so you can identify what
code the problem is in when you see the error

The line labels do not matter (Proc_Exit:, Proc_Err, I like to use the
same ones all the time -- they only have to be unique within a procedure.

if you get an error, press CTRL-BREAK when the message box pops up,
and possibly then Enter to dismiss the dialog box

this takes you into the code

right-click on the *Resume* statement
from the shortcut menu, choose --> Set Next Statement

then press F8 to resume with the statement that caused the problem --
you can fix it! -- or at least see what the problem is

pressing F8 executes one statement at a time

press F5 to continue execution automatically


~~~
While I am developing, I like to make the error handler go to the line
that caused the problem so I can see where it is. Resume goes back to
the offending line. When code Stops, press F8 to execute one statement
at a time.


Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*

 

[AccessVandal via AccessMonster.com]

In the mean time, try to remove the parenthesis “(“ while waiting for crystal
reply.

Hi Crystal,

I haven't changed the code but now when I run the filter it gives me this
error:

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

In this example, I entered Smith in the CSR unbound text box.

 

[Melissa]

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

--
Melissa

In the mean time, try to remove the parenthesis “(“ while waiting for crystal
reply.

Hi Crystal,

I haven't changed the code but now when I run the filter it gives me this
error:

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

In this example, I entered Smith in the CSR unbound text box.

 

[AccessVandal via AccessMonster.com]

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