?
Solved

MS Acc 2k7 Form FilterOn failing

Posted on 2014-04-04
21
Medium Priority
?
343 Views
Last Modified: 2014-04-07
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

0
Comment
Question by:Jim P.
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 5
  • 2
  • +3
21 Comments
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39979568
TRY FltrStr = "[QuoteStr] Like '*" &Me.FltrTxtBox & "*'"

I.E. single quote, then * outside double quotes which surround & variable &.
0
 
LVL 38

Author Comment

by:Jim P.
ID: 39980028
No luck.
0
 
LVL 7

Expert Comment

by:COACHMAN99
ID: 39980283
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 24

Expert Comment

by:Bitsqueezer
ID: 39980978
Hi,

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

Cheers,

Christian
0
 
LVL 38

Author Comment

by:Jim P.
ID: 39981560
Here's a copy of the DB.
Quotes.zip
0
 
LVL 58
ID: 39981616
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
 
LVL 19

Expert Comment

by:Richard Daneke
ID: 39981635
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
 
LVL 38

Author Comment

by:Jim P.
ID: 39981646
Jim D.

The Debug.Print FltrStr is in the code. That is where I got the samples in the original Q.
0
 
LVL 38

Author Comment

by:Jim P.
ID: 39981650
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
 
LVL 58
ID: 39981648
Ok, well not too much else to go wrong then that hasn't already been touched on.

Jim.
0
 
LVL 38

Author Comment

by:Jim P.
ID: 39981654
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
 
LVL 58
ID: 39981706
<<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
 
LVL 38

Accepted Solution

by:
Jim P. earned 0 total points
ID: 39981708
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
 
LVL 19

Assisted Solution

by:Richard Daneke
Richard Daneke earned 1000 total points
ID: 39981716
0
 
LVL 58
ID: 39981888
I got it to work fine with the asterisks

Jim
0
 
LVL 58
ID: 39981895
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
 
LVL 38

Author Comment

by:Jim P.
ID: 39981972
I was seeing the ALike as well, but it still worked with the changed wildcards.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 39983592
@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
 
LVL 38

Author Comment

by:Jim P.
ID: 39983647
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
 
LVL 26

Expert Comment

by:Nick67
ID: 39983731
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
 
LVL 38

Author Comment

by:Jim P.
ID: 39983789
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Suggested Courses

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question