SQL Like not working correctly when string contains #

[Gina]

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

 

[Dirk Goldgar]

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')")

 

[gina_h]

Escape -- I knew there was a word for what I wanted to do to those wildcards!!
For the life of me, I couldn't think of it when I was writing the post.
Thanks for understanding what I meant. And the code was great, I just had to
modify it slightly. This is what I used:

Function PrepForLike(pValue As Variant) As String

Dim strResult As String

If Not IsNull(pValue) Then
strResult = Replace(pValue, "[", "[[]")
strResult = Replace(strResult, "*", "[*]")
strResult = Replace(strResult, "?", "[?]")
strResult = Replace(strResult, "#", "[#]")


PrepForLike = strResult
End If

End Function

Thanks again!

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

[quoted text clipped - 17 lines]

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(pValue, "[", "[[]")
strResult = Replace(strResult, "*", "[*]")
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]

Escape -- I knew there was a word for what I wanted to do to those
wildcards!! For the life of me, I couldn't think of it when I was
writing the post. Thanks for understanding what I meant. And the code
was great, I just had to modify it slightly. This is what I used:

Function PrepForLike(pValue As Variant) As String

Dim strResult As String

If Not IsNull(pValue) Then
strResult = Replace(pValue, "[", "[[]")
strResult = Replace(strResult, "*", "[*]")
strResult = Replace(strResult, "?", "[?]")
strResult = Replace(strResult, "#", "[#]")


PrepForLike = strResult
End If

End Function

Argh! I moved one of the lines and broke it! Sorry about that; I'm
glad you spotted the problem.