By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,740 Members | 1,326 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,740 IT Pros & Developers. It's quick & easy.

Search Query isn't working for certain results

P: 11
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?
Mar 4 '15 #1
Share this Question
Share on Google+
6 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,486
Try this:

Expand|Select|Wrap|Line Numbers
  1. Like "'*" & [Forms]![Search Form]![Keyword] & "*'"
The missing single quotes may have been causing problems...
Mar 4 '15 #2

P: 11
That solution didn't work, it also made other words stop working too. Do you have any other ideas?
Mar 4 '15 #3

twinnyfo
Expert Mod 2.5K+
P: 3,486
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSearch_Click()
  2.     Dim strSQL As String
  3.  
  4.     strSearch = ""
  5.     strSearch = "LIKE '*" & Me.Keyword & "*'"
  6.     strSQL = "SELECT Field1, Field2 " & _
  7.         "FROM Table1 " & _
  8.         "WHERE Field3 LIKE '*" & Me.Keyword & "*';"
  9.     DoCmd.RunSQL strSQL
  10. 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.
Mar 4 '15 #4

P: 11
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.
Mar 4 '15 #5

Rabbit
Expert Mod 10K+
P: 12,430
The hash symbol # is a wildcard. In a like statement, it matches any numeric character.
Mar 4 '15 #6

jforbes
Expert 100+
P: 1,107
Your gonna get some weird results when you bump up against Reserved Characters/wildcards like:
Expand|Select|Wrap|Line Numbers
  1. #
  2. '
  3. "
  4. ?
  5. %
  6. ^
  7. _
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:
Expand|Select|Wrap|Line Numbers
  1. Public Function trimForSQLWhere(ByVal sTemp As String) As String
  2.     trimForSQLWhere = trimWithRegex(sTemp, "[^a-zA-Z0-9\\@~()\: .,_\-\t\n\r\/]")
  3. End Function
  4. Public Function trimWithRegex(ByVal sTemp As String, ByRef sPattern As String) As String
  5.     Dim RegEx As Object    
  6.     Set RegEx = CreateObject("VBScript.RegExp")
  7.     RegEx.Global = True
  8.     RegEx.Pattern = sPattern
  9.     trimWithRegex = RegEx.Replace(sTemp, "")
  10. 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/
Mar 4 '15 #7

Post your reply

Sign in to post your reply or Sign up for a free account.