Search Query isn't working for certain results

[amwkl4]

Hi all,

The program I am using is Microsoft Access 2010. I have a search form set up where I can type criteria in a textbox (Changed named to Keyword) and press a button to run a query. The Criteria for the query is is

Like "*" & [Forms]![Search Form]![Keyword] & "*"

I have a list item in the keywords column called "Port au Port #1 wells" (It's a local oil and gas well where I live) and when I put that exact text in the textbox I have set up for the search query, it says there are no matches. However, everything else I search without the "#" sign seems to return the correct number of records. Why is this happening?

[twinnyfo]

Try this:

Like "'*" & [Forms]![Search Form]![Keyword] & "*'"

The missing single quotes may have been causing problems...

[amwkl4]

That solution didn't work, it also made other words stop working too. Do you have any other ideas?

[twinnyfo]

Private Sub cmdSearch_Click()
    Dim strSQL As String
 
    strSearch = ""
    strSearch = "LIKE '*" & Me.Keyword & "*'"
    strSQL = "SELECT Field1, Field2 " & _
        "FROM Table1 " & _
        "WHERE Field3 LIKE '*" & Me.Keyword & "*';"
    DoCmd.RunSQL strSQL
End Sub

This creates a SQL string and executes it. Without seeing your exact query and knowing exactly what you are trying to look for, this is really just stabbing in the dark for a solution.

[amwkl4]

Thanks for your time, I have finally found a solution. It is very simple, I just have to enclose the # with square brackets like so [#] when inputting the text in the textbox.

[Rabbit]

The hash symbol # is a wildcard. In a like statement, it matches any numeric character.

[jforbes]

Your gonna get some weird results when you bump up against Reserved Characters/wildcards like:

#
'
"
?
%
^
_

I would recommend removing these characters out of the Where Clause before attempting to filter on them or else your program will return errors to you users, which will make it look as though the program is poorly written.

I use this trimForSQLWhere() function to accomplish this. It looks kinda of daunting at first, but it's not really that bad:

Public Function trimForSQLWhere(ByVal sTemp As String) As String
    trimForSQLWhere = trimWithRegex(sTemp, "[^a-zA-Z0-9\\@~()\: .,_\-\t\n\r\/]")
End Function
Public Function trimWithRegex(ByVal sTemp As String, ByRef sPattern As String) As String
    Dim RegEx As Object    
    Set RegEx = CreateObject("VBScript.RegExp")
    RegEx.Global = True
    RegEx.Pattern = sPattern
    trimWithRegex = RegEx.Replace(sTemp, "")
End Function

It will remove most crazy characters and leave AlphaNumerics and some other things like Carriage Returns, Line Feeds and slashes.

For a neat reference on Regular Expressions, check out http://www.regexr.com/