• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 638
  • Last Modified:

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

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
IEHP1
Asked:
IEHP1
  • 8
  • 7
1 Solution
 
kmslogicCommented:
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
 
IEHP1Author Commented:
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
 
kmslogicCommented:
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
IEHP1Author Commented:
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
 
kmslogicCommented:
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
 
IEHP1Author Commented:
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
 
kmslogicCommented:
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
 
IEHP1Author Commented:
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
 
kmslogicCommented:
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
 
kmslogicCommented:
The reason entering 1423 works is because your code is putting asterisks around it so it is actually setting the filter to *1423*
0
 
IEHP1Author Commented:
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
 
IEHP1Author Commented:
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
 
kmslogicCommented:
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
 
IEHP1Author Commented:
This person actually took the time to explain how it works to me which is the most important thing!!!!
0
 
kmslogicCommented:
Thank you, glad it's working!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now