Solved

MS Acc 2k7 Form FilterOn failing

Posted on 2014-04-04
21
338 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.
  • 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
 
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 57
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 18

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

Jim.
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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 57
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 18

Assisted Solution

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

Jim
0
 
LVL 57
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
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 …

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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now