Filtering by control

[Jody]

I am missing something and it seems like it would be something simple. Below
are two sample codes that I am working with. They both "work" but not quite
as intended. I have a few unbound text boxes at the top of my form that I
use for the filter criteria. At some point in time, I discovered how to do
this with a "LIKE" operator.

This first bit code works fantastically, but it will return a range of
values - because of the "LIKE" operator. Say my criteria is to filter for
machine 2, it will return 2, 12, 20, 21, 22 and so forth. I only want to see
records for maching 2.

If Not IsNull(Me.FilterMachine) Then
strWhere = strWhere & "([Machine Number] Like ""*" & Me.FilterMachine &
"*"") And "
End If

This second bit of code will in fact "work" (only displaying records for
machine 2)however, it generates a popup requiring me to enter the filter
information into that popup, instead of taking the criteria from the control.

If Not IsNull(Me.FilterMachine) Then
strWhere = strWhere & "([Machine Number] = Me.FilterMachine) And "
End If

My question is: How do I get the "=" operator functionality to accept the
criteria privided in the contron on the form (like the first code snippit
does), rather than throwing a "Required parameter value" popup for
Me.FilterMachine?

What did I miss?

-Jody

 

[Dirk Goldgar]

I am missing something and it seems like it would be something simple.
Below
are two sample codes that I am working with. They both "work" but not
quite
as intended. I have a few unbound text boxes at the top of my form that I
use for the filter criteria. At some point in time, I discovered how to
do
this with a "LIKE" operator.

This first bit code works fantastically, but it will return a range of
values - because of the "LIKE" operator. Say my criteria is to filter for
machine 2, it will return 2, 12, 20, 21, 22 and so forth. I only want to
see
records for maching 2.

If Not IsNull(Me.FilterMachine) Then
strWhere = strWhere & "([Machine Number] Like ""*" & Me.FilterMachine &
"*"") And "
End If

This second bit of code will in fact "work" (only displaying records for
machine 2)however, it generates a popup requiring me to enter the filter
information into that popup, instead of taking the criteria from the
control.

If Not IsNull(Me.FilterMachine) Then
strWhere = strWhere & "([Machine Number] = Me.FilterMachine) And "
End If

My question is: How do I get the "=" operator functionality to accept the
criteria privided in the contron on the form (like the first code snippit
does), rather than throwing a "Required parameter value" popup for
Me.FilterMachine?

What did I miss?

The first one gives undesirable results because you are making a text
comparison and using wild-card matching. The second one doesn't work
because "Me" is not understood in a query.

I think maybe your best bet is to write this:

If Not IsNull(Me.FilterMachine) Then
strWhere = strWhere & _
"([Machine Number] = " & Me.FilterMachine & ") And "
End If

That works if [Machine Number] is a numeric field, not a text field.

 

[Jody]

100% right on! Though I don't understand "_" and I see where I missed a
couple of "&'s" in my previous statement, this works perfectly. Thank you
for your help.

 

[Dirk Goldgar]

100% right on! Though I don't understand "_" and I see where I missed a
couple of "&'s" in my previous statement, this works perfectly. Thank you
for your help.

The underscore (_) is just a line-continuation character, and allows you to
continue a VBA statement onto another line.