Apostrephes Make Query Upset

[mcwhirt3]

Hi,

I'm having what seems like a common problem where ' (apostrephes)
with a record are causing trouble when run through a query. I've read
about double quote putting the data theough if..then statements etc,
but have not been able to get anything to work. I'm making a list of
cheeses and some have apostrephes in their name i.e. (Itay's Finest).
they are listed in the "name" column of tbl_cheese. Any ideas for what
to do. Thank you for any help!!!

<-------code start------->

Private Sub cmdRunReport_Click()
On Error GoTo Err_cmdRunReport_Click
Dim MyDB As DAO.Database
Dim qdf As DAO.QueryDef
Dim i As Integer, strSQL As String
Dim strWhere As String, strIN As String
Dim flgAll As Boolean

Set MyDB = CurrentDb()

strSQL = "SELECT tbl_countries.flag_image, * FROM tbl_countries
INNER JOIN tbl_cheese ON tbl_countries.flag_name = tbl_cheese.origin"



'create the IN string by looping thru the listbox
For i = 0 To lstLocalAuthority.ListCount - 1
If lstLocalAuthority.Selected(i) Then
If lstLocalAuthority.Column(0, i) = "All" Then
flgAll = True
End If
strIN = strIN & "''" & lstLocalAuthority.Column(0, i) & "'
',"""
End If
Next i

'create the WHERE string, stripping off the last comma of the IN
string
strWhere = " WHERE [name] in (" & Left(strIN, Len(strIN) - 1) & ")"

'if "All" was selected, don't add the WHERE condition
If Not flgAll Then
strSQL = strSQL & strWhere
End If

MyDB.QueryDefs.Delete "qrycheeselist"
Set qdf = MyDB.CreateQueryDef("qrycheeselist", strSQL)

DoCmd.OpenReport "qrycheeselist", acPreview
end sub

<-------code end------->

 

[Douglas J. Steele]

It's any occurrence of a single quote in the text itself that needs to be
doubled, not preceding ones.

strIN = strIN & "'" & Replace(lstLocalAuthority.Column(0, i), "'", "''") &
"', "

Exagerated for clarity, that's

strIN = strIN & " ' " & Replace(lstLocalAuthority.Column(0, i), " ' ", " '
' ") & " ' , "

 

[mcwhirt3]

Tha k you Doug...you are the man! Got it.