"Keith" <ke*********@baeAWAYWITHITsystems.com> wrote in message
news:42**********@glkas0286.greenlnk.net...
I've tried various combinations of quotation marks and asterisks but I
can't see to get the syntax right. Could someone post a working example?
Many thanks.
Keith.
The apostrophes don't normally cause a problem because the string is wrapped
in double quotes, eg
SELECT * FROM tblAlbum WHERE tblAlbum.TrackList LIKE "*Joe's Garage*"
Things would only go wrong when you looked for Pete "The Punk" Smith where
you would have to double up the speech marks. You could try pasting this
into a new module, which would let you simply have:
strSQL = "SELECT * FROM tblAlbum WHERE
tblAlbum.TrackList=AddQuotes(strCriterion,DoubleQu ote)
or
strCriterion = "*" & strCriterion & "*"
strSQL = "SELECT * FROM tblAlbum WHERE tblAlbum.TrackList LIKE
AddQuotes(strCriterion,DoubleQuote)
Option Compare Database
Option Explicit
Public Enum QuoteType
NoQuote
SingleQuote
DoubleQuote
End Enum
Public Function AddQuotes(strValue, Q As QuoteType) As String
Dim strReturn As String
Select Case Q
Case QuoteType.SingleQuote
strReturn = Replace(strValue, "'", "''")
strReturn = "'" & strReturn & "'"
Case QuoteType.DoubleQuote
strReturn = Replace(strValue, """", """""")
strReturn = """" & strReturn & """"
Case Else
strReturn = strValue
End Select
AddQuotes = strReturn
End Function
