MS Acc 2k7 Form FilterOn failing

This should be some <bad word> simple code. I know I have done it in the past but it isn't working. I'm building an Access 2007 DB just for practice, It's been compacted and repaired. The control names have been changed from the field names.

In the form footer I have an unbound text box and a button. The idea is to enter text into the box and then click on the "Filter" button and then it will limit the items on the form based on the string. When I click on the "Filter" button I get nothing, but I know there is data that contains the keywords.

I even went through and installed the 2K7 SP3 and it is still failing.

Private Sub FilterOn_Click()

Dim FltrStr As String

FltrStr = "[QuoteStr] Like " & Chr(34) & "*" & Me.FltrTxtBox & "*" & Chr(34)
Debug.Print FltrStr

With Me
     .Filter = FltrStr
     .FilterOn = True
End With

End Sub

Open in new window


Some of the filter examples:
[QuoteStr] Like "*press*"
[QuoteStr] Like "*law*"
[QuoteStr] Like "*christmas*"

Open in new window

LVL 38
Jim P.Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Jim P.Connect With a Mentor Author Commented:
I figured it out. It's the wildcards. It is set to use the ANSI-92 SQL options. That changes the wildcards to be the percent and underscore compared to the Access asterisk and question mark.

Under the Access Start icon --> Access Options --> Object Designers is where you would change that.

Private Sub FilterOn_Click()

Dim FltrStr As String

FltrStr = "[QuoteStr] Like " & Chr(34) & "%" & Me.FltrTxtBox & "%" & Chr(34)
Debug.Print FltrStr

With Me
     .Filter = FltrStr
     .FilterOn = True
End With

End Sub

Open in new window

0
 
COACHMAN99Commented:
TRY FltrStr = "[QuoteStr] Like '*" &Me.FltrTxtBox & "*'"

I.E. single quote, then * outside double quotes which surround & variable &.
0
 
Jim P.Author Commented:
No luck.
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
COACHMAN99Commented:
I just created a table, form etc and plugged in your code and mine, and both work fine.
Problem must be external.

FltrStr = "[QuoteStr] Like '*" & Me.fltrtxtbox & "*'"
0
 
BitsqueezerCommented:
Hi,

maybe you've set the "AllowFilter" property of the form to False?

Cheers,

Christian
0
 
Jim P.Author Commented:
Here's a copy of the DB.
Quotes.zip
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Nothing wrong with your code.

You can debug:

1. Put a stop in the code just before

    .Filter = FltrStr

 then hover over the variable to get the contents, or bring up the debug window (Ctrl/G) and type:

 ? FlrStr

followed by a return.

 See what the filter is actually being set to.  Then use F8 to step the code through.

 You can check the filter setting at filter on by doing:

? Me.Filter

 followed by a return

 If that all looks good and no errors, then I would look to the forms underlying recordset, what it returns, and if the filter is actually failing or not.

Also make sure your not looking at the form in layout view.   You want normal form view.

Jim.
0
 
Richard DanekeTrainerCommented:
Is the [QuoteStr] necessary?  In your sample database the filter comes up as
Alike '*<my text>*'  
You can see the filter by opening Advanced Filter/Sort.
0
 
Jim P.Author Commented:
Jim D.

The Debug.Print FltrStr is in the code. That is where I got the samples in the original Q.
0
 
Jim P.Author Commented:
Is the [QuoteStr] necessary?

I'm trying to have the ability to fill in the text box at the bottom of the form and then filter off that. I don't want to go to the Advanced Filter to build it.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Ok, well not too much else to go wrong then that hasn't already been touched on.

Jim.
0
 
Jim P.Author Commented:
That's why I'm frustrated with this. I think it should be a straightforward issue. Unless there is a feature in Acc 2007 that they haven't noted on filters.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<Ok, well not too much else to go wrong then that hasn't already been touched on.>>

 Famous last words<g>

 First, sorry I didn't realize who was asking the question when I first commented.

 Second, the DB is messed up because of auto correct.   Open tools options, turn off all auto correct options under current DB and proofing.

 Then close the DB and Access.  re-open and create a blank DB.  Import your table and form.

 Open the form in design view and name your filter button something other than "FilterOn" (which is a form property).   Save.

 Confirm the problem is gone by:

1. Create a new query.
2. Add your table.
3. Try placing a criteria of:

 Like "*A*"  

 on the quote string field.

 On the DB you uploaded, every time I did that, it was automatically translated to:

 ALike "*A*"

  which is why your filter wasn't working.

  I don't think the filter button name had anything to do with it, but it's not a good idea anyway to name a button the same as a form property.

Jim.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
I got it to work fine with the asterisks

Jim
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
Actually, I may have turned off the ANSI 92 setting.

Something definitely wrong with the db you uploaded though.   Got 'ALike'. In the query designer no matter what I did.

Jim
0
 
Jim P.Author Commented:
I was seeing the ALike as well, but it still worked with the changed wildcards.
0
 
Nick67Commented:
@JimP
You got it fixed and that's good.
Myself, I would have opened the underlying query and thrown the parameter into it to test
Did the query editor fool you and accept Like "*" &  [enter it]  & "*" without flipping its lid?
Did the query editor hate Like "*christmas*" ?

Because my typing skills aren't great, I build most of my SQL in the query editor and paste it into the VBA--and if I am building something kitschy I can pre-determine that the SQL would work that way too.
I wouldn't have caught the ANSI 92 setting because I never play with that.  In my pass-through I change the syntax by hand.  Why would you want the ANSI 92 syntax?
0
 
Jim P.Author Commented:
Because my typing skills aren't great, I build most of my SQL in the query editor and paste it into the VBA--and if I am building something kitschy I can pre-determine that the SQL would work that way too.

I do it both ways. But I've been doing T-SQL for years so I've had to get my typing skills better. Some of the really weird queries just can't be done in Access design view, like when you have an if statement in the joins.

Why would you want the ANSI 92 syntax?

I have a copy of SQL 2008 installed on my computer, so I think it is a matter of when Office installed it defaulted that way. Just a guess.
0
 
Nick67Commented:
Access 2007 :o

I found it completely unusable with the Ribbon the way it was.
Finding 'Compact and Repair' was not at all intuitive, no?
A2010+ are much better organized and configurable when it comes to the Ribbon.

Be aware that any object created in A2007+ gets corrupted in short order if edited afterward by A2003.  That's one of the three great unhappinesses of A2007+

Nick67
0
 
Jim P.Author Commented:
I prefer Acc 2k3 as well. I've built many things with it.

But my company that I'm working for only had 2k7 before we were bought out. And the new company is going to go 2k10 in a few months and I'm being integrated more into my new department. So I figured it was time to start practicing the steps to see what I can build with it.
0
All Courses

From novice to tech pro — start learning today.