M
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------->
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------->