Solved

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

Posted on 2013-01-27
15
575 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
  • 8
  • 7
15 Comments
 
LVL 16

Accepted Solution

by:
kmslogic earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:IEHP1
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:IEHP1
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thank you, glad it's working!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

772 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

10 Experts available now in Live!

Get 1:1 Help Now