Solved

Form Filter Unbound Text box Called Street can't Find Results with # in Street Text Field

Posted on 2013-01-27
15
614 Views
Last Modified: 2013-01-27
So I have now solved 3 problems with my form filter thanks to everyone's help.....

I believe the very last problem is as follows:

In the form header section is an Unbound Text box called txtStreet (which is a text data type in the table). The VBA code is using an If statement to treat it as such like a text field should be treated.

The problem comes along when I try to input one of the Street names that have a '#' symbol somewhere in it. If I type in the first part of the Street, it will work and find it in the Results section, but if I include the whole Street field, it will not find it, when it should find it.

I am attaching the sample database to ask for help for anyone who would be so kind to help me out with the VBA on this. There is only one person in the Query results with a '#' symbol in their Street field (so this would be the test person).

Thank you
Form-Building-Using-FiltersRev-4.accdb
0
Comment
Question by:IEHP1
[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
  • 8
  • 7
15 Comments
 
LVL 16

Accepted Solution

by:
kmslogic earned 500 total points
ID: 38825670
You can't use the # as a literal in a like statement because it substitutes for any single numeric digit, which is why it returns all records when you put a # in the street field.

You can work around this by surrounding any # or * in the search string with square brackets.

A function can do it like

Function FixSearch(ByVal pstrValue As String) As String
    pstrValue = Replace(pstrValue, "*", "[*]")
    pstrValue = Replace(pstrValue, "#", "[#]")
    pstrValue = Replace(pstrValue, "?", "[?]")
    FixSearch = pstrValue
End Function 

Open in new window


And changing your line to

strWhere = strWhere & "([Street] Like ""*" & FixSearch(Me.txtStreet) & "*"") AND "

Open in new window

0
 

Author Comment

by:IEHP1
ID: 38825726
I see kmslogic, I am learning these VBA functions and capabilities as I go along here and see the function you noted can be very useful.

I copied and pasted it into the command button's onclick event but then when I chose one of the "Providers" in the first combo box and hit the command button to filter the results, it came up with many errors (many errors as I tried to fix it).

So I downloaded the database I uploaded and just copied and pasted the code changes you said to make so that I can attach it here and you can see if I inserted it into the right section of the onclick event (or something like that because I am not sure why it wouldn't be working??)
Form-Building-Using-FiltersRev-4.accdb
0
 
LVL 16

Expert Comment

by:kmslogic
ID: 38825737
Yes you can't nest a function inside of a Sub,  So take it out of the onclick event (don't forget the End Function you have at the bottom of the onclick sub right before end sub).

You can either put it in a new module, or just stuff it at the bottom of your form code--

Private Sub Form_Open(Cancel As Integer)
    'Remove the single quote from these lines if you want to initially show no records.
    Me.Filter = "(False)"
    Me.FilterOn = True
End Sub


Function FixSearch(ByVal pstrValue As String) As String
    pstrValue = Replace(pstrValue, "*", "[*]")
    pstrValue = Replace(pstrValue, "#", "[#]")
    pstrValue = Replace(pstrValue, "?", "[?]")
    FixSearch = pstrValue
End Function

Open in new window

0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

Author Comment

by:IEHP1
ID: 38825744
I see now. So I got it to work now and that is just awesomely great!!

So at first I thought the ? and the * were like wildcards so I tested it as in 456 Maple Ave. ?24 and then 456 Maple Ave. *24, but that didn't yield any results.

So I guess those were included in the Function to mean that I could put in other symbols that might happen to be in the data so that it would still yield the result if say, 123 Park St. *24 was an actual address in the data of the table(s).

Is that right?
0
 
LVL 16

Expert Comment

by:kmslogic
ID: 38825749
Yes that's right.  You can remove those lines from the function if you'd like to be able to use those wildcards directly.
0
 

Author Comment

by:IEHP1
ID: 38825761
It's good to know I am right about something, but I don't believe I could use those wildcards in the database as in putting * in Street field and getting all results that aren't null in the Street field or putting ? in Street field in a particular position to get the result I would want?

Does this make sense?
0
 
LVL 16

Expert Comment

by:kmslogic
ID: 38825770
I'm not sure I understand, but if you remove the replace lines for * and ? in the function then you can do something like this in the street field:

4*3

and it will return the lines for 4113 Oracle and 1423 Rogers for example.
0
 

Author Comment

by:IEHP1
ID: 38825779
I know your example would work in an Access query, but I tried 14*3 Rogers Way which should have matched to one of my sample records, but didn't yield any results.

Not exactly sure what is preventing this? Is it the default behavior to yield the results with the wildcards?

But I could type in 1423 and get the result it should get without having to enter the rest of the Street.......
0
 
LVL 16

Expert Comment

by:kmslogic
ID: 38825784
I tried your query on my copy of your database and it works

14*3 Rogers Way

Make sure you remove the lines from the function so it lets the asterisk pass through:

Function FixSearch(ByVal pstrValue As String) As String
    pstrValue = Replace(pstrValue, "#", "[#]")
    FixSearch = pstrValue
End Function

Open in new window


And yes that's the default behavior.  * will match 1 or more characters, ? will match a single character and # will match a single numeric character.
0
 
LVL 16

Expert Comment

by:kmslogic
ID: 38825786
The reason entering 1423 works is because your code is putting asterisks around it so it is actually setting the filter to *1423*
0
 

Author Comment

by:IEHP1
ID: 38825791
I see, I forgot to take those lines out from the function.

And also, the # will match a single numeric character unless you have the line in the function as it stands right now, right? Because it is now using # as a literal instead of replacing it with any numeric character?

Am I right about this?
0
 

Author Comment

by:IEHP1
ID: 38825801
I got it now!! and I understand it how it works which is the most important part....

I thank you so very much for taking the time to explain how it works to me!!!
0
 
LVL 16

Expert Comment

by:kmslogic
ID: 38825803
Yes that's right -- it is letting you use it as a literal because of the line in the function which is replacing it with [#]

So even if you removed the function totally you could still search for a literal # by typing [#] in your search field.
0
 

Author Closing Comment

by:IEHP1
ID: 38825805
This person actually took the time to explain how it works to me which is the most important thing!!!!
0
 
LVL 16

Expert Comment

by:kmslogic
ID: 38825818
Thank you, glad it's working!
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

730 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