Full Version: clarify syntax for Me.Filter method
UtterAccess Forums > Microsoft® Access > Access Forms
I need a little help to clarify the syntax to turn on a particular filter (using the me.filter = "SomeCriteriaHere"; me.filteron=true method) on the [orderid] control of a continuous form.
The filter gets its source from a combo box ("cmbFilterByOrderNumber") in the form's header. That combo gets its source data from a self-lookup into the [orderid] field of the parent table.
I have previously used a concatenation to get some wildcard action in the combo:
Me.Filter = "[orderid] like '*" & Me.cmbFilterByOrderNumber & "*' "
Now I want to remove the wildcards so as to limit the filter to exact matches. I have found the following to work:
Me.Filter = "[orderid] like ' " & Me.cmbFilterByOrderNumber & " ' "
(I simply removed the *'s).
Is there a way that I can remove the concatenating? When learning to make this string I found that I can enter text or numbers manually into the filter like:
Me.Filter = "[orderid] like '*" & "o" & "*'" <- will return anything with on "o" in it,
Me.Filter = "[orderid] like '" & "now" & "'" <- will return exact matches of "now",
Me.Filter = "[orderid] ='" & "now" & "'" <- will return exact matches of "now",
Me.Filter = "[orderid] ='now'" <- will return exact matches of "now",
Why can't I have this:
Me.Filter = "[orderid] = 'Me.cmbFilterByOrderNumber' "
to get an exact match? (Presumably it's looking to exactly match the text "Me.cmbFilterByOrderNumber"). Must I only use the code in red, above?
Jerry Dennison
Yes, you will need to use the code in red. The reason is that you are essentially providing a variable as the criteria. Since the variable value is contained in a form control you must reference the form control (i.e. concatenate the form control's value to your filter string).

You need to use:

Me.Filter = "[orderid] = " & Me.cmbFilterByOrderNumber

This assumes that the item cmbFilterByOrderNumer is a valid control on the current form (Me object) and the item is numeric.

Use this:

Me.Filter = "[orderid] = " & " ' " & Me.cmbFilterByOrderNumber & " ' "

if the item is text.

And This:

Me.Filter = "[orderid] = " & " #" & Me.cmbFilterByOrderNumber & "# "

if the item is date.

Edited by: Jerry Dennison on Mon Jan 23 10:36:23 EST 2006.
Edited by: Jerry Dennison on Mon Jan 23 10:46:32 EST 2006.
To expand on Jerry's answer, you need to use the concatenation to FORCE Access to fetch the value in the control. If you don't concatenate, then it will use the TEXT of the control reference.
For Text I use:
e.Filter = "[orderid] = " & Chr(34) & Me.cmbFilterByOrderNumber & Chr(34)
Hi Jerry,
Thanks. I initially saw your unedited response and gave that a shot, it didn't work as the field is text. Then I tried which oddly didn't work either (maybe I'm misunderstanding something?).
No matter, as I took your first line of advice and stuck with the code I had in red. Thanks for the prompt response!
Whoa! That all happened real fast! I didn't even see Frank's or Boyd's responses before I posted back.
rank: thanks, I undertand now.
Boyd: yep, that also worked, but I don't know what the Chr(34)'s are / do. (I have used linefeed & carriage return before, but was never able to extract a list of the various other codes from Access...).
Jerry Dennison
That's because I screwed up and left off the concatenation (what I get for rushing).
hould be:
Me.Filter = "[orderid] = " & " ' " & Me.cmbFilterByOrderNumber & " ' "
for text.
Jerry Dennison
Chr(34) is the double quote character. You can get a list of all the ANSI characters by searching Access Help.
e.Filter = "[orderid] = " & " ' " & Me.cmbFilterByOrderNumber & " ' "
didn't work either. No biggie, I've now got several options that work fine. I was really more on a learning track here, wanting to understand the nuts & bolts.
Looks like I've gotta do this more on Monday mornings, it's a VIP bonanza right now!
Jerry Dennison
That should work unless something is misspelled.
nyway, you can eliminate an ampersand by using the following:
Me.Filter = "[orderid] = ' " & Me.cmbFilterByOrderNumber & " ' "
Freaky, it's still returning an empty set. No matter, the other code works great. Thanks for all the help.
Jerry Dennison
You may need to remove the extra spaces after the single quotes, I put them there for clarity.
e.Filter = "[orderid] = '" & Me.cmbFilterByOrderNumber & "'"
that did the trick!
Jerry Dennison
You're welcome.
Good luck!
Glad you got it working.
I use the Chr(34) in posts and for training for readability. It is easier to see than '" whcih is a ' followed by ".
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.