Re: SQL Like not working correctly when string contains #

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

"Gina" <u23622@uwe> wrote in message 628e574a1aa60@uwe">news:628e574a1aa60@uwe
I am using Access 2003 to create a messaging program based on XML
documents received from GPS units. I am forced to use the unit number
and message text to match up incoming XML documents with my database
records. I use the following SQL statement:

"SELECT * FROM tblMessageOutbox WHERE VehicleID = " & _
objXMLDoc.selectNodes(strRootNode &
"/VEHICLE_LABEL").Item(intCounter). nodeTypedValue() & _
" AND MessageText LIKE '" & Left(objXMLDoc.selectNodes(strRootNode &
"/ORIG_OUTBOUND_MESSAGE").Item(intCounter).nodeTypedValue(), 10) & _
"*' AND (SendStatus = 'Sending' or SendStatus = 'Queued')")

The problem I ran into today is that one of the messages contained
the # character. This character was treated as a wildcard and not a
literal. This prevented the matching record from being found. I
assume that this will be a problem with any of the wildcard
characters. What do I need to add to my code so that wildcard
characters in the search string will not be treated as wildcards?

Thank you,
Gina


You can "escape" the wild-card characters by enclosing them in square
brackets. So you might try using this function:

'----- start of untested code -----
Function PrepForLike(pValue As Variant) As String

Dim strResult As String

If Not IsNull(pValue) Then

strResult = Replace(strResult, "[", "[[]")
strResult = Replace(pValue, "*", "[*]")
strResult = Replace(strResult, "?", "[?]")
strResult = Replace(strResult, "#", "[#]")

PrepForLike = strResult

End If

End Function

'----- end of untested code -----

In the context of the code you posted, you'd call it like this:

"SELECT * FROM tblMessageOutbox WHERE VehicleID = " & _
objXMLDoc.selectNodes(strRootNode & "/VEHICLE_LABEL").Item(intCounter).
nodeTypedValue() & _
" AND MessageText LIKE '" & _
PrepForLike( _
Left(objXMLDoc.selectNodes(strRootNode &
"/ORIG_OUTBOUND_MESSAGE").Item(intCounter).nodeTypedValue(), 10) _
) _
& "*' AND (SendStatus = 'Sending' or SendStatus = 'Queued')")


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

Relevant Pages

  • Re: CONTAINS and non-wildcard asterisk
    ... >> wildcard it, and you wrap it in single quotes. ... >> If you wrap the phrase with double quotes you will get wildcarding, but it>> will still match with test*. ... >> alphanumeric characters it strips them out and indexes the alphanumeric ... >> Looking for a book on SQL Server replication? ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Distinguishing hard spaces from regular spaces
    ... Just change to .MatchWildcards = True. ... A wildcard search using a space character in the Find.Text expression won't ... There's a bug that reverses the replacement when you use the \1 code, ... characters need an unbreakable space before ...
    (microsoft.public.word.vba.general)
  • Optimal wildcard search algorithm
    ... whether or not one or more users exist, given some wildcard expression. ... but we'll focus on username brute-forcing for this ... One approach to finding all usernames would be a kind of breadth-first ... and determine which characters exist in the first position. ...
    (Pen-Test)
  • Re: Census ennumeration districts
    ... You searched for Blanche Jones 24 hits ... The wildcard query resulted in too many matches. ... Use more characters before the wildcard. ...
    (soc.genealogy.britain)
  • RE: remove last three characters of cell
    ... "Ray A" wrote: ... I am trying to use the same formula to remove the last three characters ... >> specify the letter as a wildcard, ... If it is not a comma then will just return the entire ...
    (microsoft.public.excel.misc)