"Keith" <ke*********@ba eAWAYWITHITsyst ems.com> wrote in message
news:42******** **@glkas0286.gr eenlnk.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.TrackL ist 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.TrackL ist=AddQuotes(s trCriterion,Dou bleQuote)
or
strCriterion = "*" & strCriterion & "*"
strSQL = "SELECT * FROM tblAlbum WHERE tblAlbum.TrackL ist LIKE
AddQuotes(strCr iterion,DoubleQ uote)
Option Compare Database
Option Explicit
Public Enum QuoteType
NoQuote
SingleQuote
DoubleQuote
End Enum
Public Function AddQuotes(strVa lue, Q As QuoteType) As String
Dim strReturn As String
Select Case Q
Case QuoteType.Singl eQuote
strReturn = Replace(strValu e, "'", "''")
strReturn = "'" & strReturn & "'"
Case QuoteType.Doubl eQuote
strReturn = Replace(strValu e, """", """""")
strReturn = """" & strReturn & """"
Case Else
strReturn = strValue
End Select
AddQuotes = strReturn
End Function