syntax error apostrophe in criteria

[Marianne]

Following is the code I am using to run a query based on the criteria from a
multi select list box. Everything works perfectly until there is an
apostrophe in the criteria. Can someone help me with a workaround.
This is my code
For Each varItem In Me!lstClient.ItemsSelected
strCriteria = strCriteria & ",'" & Me!lstClient.ItemData(varItem) &
"'"
Next varItem
' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
' Build the new SQL statement incorporating the string

strSQL = "INSERT INTO tblInvNos ( CustID, ClientID, StartDate, EndDate,
InvDate ) " & vbCrLf & _
"SELECT qry_invprep.Cust, qry_invprep.ClientID, Forms!frInvSelect!StartDate
AS Start, Forms!frInvSelect!EndDate AS [End], Forms!frInvSelect!InvDate AS
Inv " & vbCrLf & _
"FROM qry_invprep " & vbCrLf & _
"WHERE qry_invprep.ClientID IN(" & strCriteria & ")" & vbCrLf & _
"GROUP BY qry_invprep.Cust, qry_invprep.ClientID;"

 

[Stefan Hoffmann]

hi Marianne,

Following is the code I am using to run a query based on the criteria from a
multi select list box. Everything works perfectly until there is an
apostrophe in the criteria. Can someone help me with a workaround.

You may take a look at BuildCriteria. Basically you need to escape the
quote in the string, I'd prefer this simple solution:

Public Function SQLQuote(AString As String, _
Optional ADelimiter As String = "'" _
) As String

SQLQuote = ADelimiter & _
Replace(AString, ADelimiter, ADelimiter & ADelimiter) & _
ADelimiter

End Function

Then you can use it:

strCriteria = strCriteria & ", " & SQLQuote(lstClient.ItemData(varItem))


mfG
--> stefan <--

 

[Marianne]

thank you so much. That did the trick beautifully.

 

[Stuart McCall]

Following is the code I am using to run a query based on the criteria from
a
multi select list box. Everything works perfectly until there is an
apostrophe in the criteria. Can someone help me with a workaround.
This is my code
For Each varItem In Me!lstClient.ItemsSelected
strCriteria = strCriteria & ",'" & Me!lstClient.ItemData(varItem) &
"'"
Next varItem
' Check that user selected something
If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If
' Remove the leading comma from the string
strCriteria = Right(strCriteria, Len(strCriteria) - 1)
' Build the new SQL statement incorporating the string

strSQL = "INSERT INTO tblInvNos ( CustID, ClientID, StartDate, EndDate,
InvDate ) " & vbCrLf & _
"SELECT qry_invprep.Cust, qry_invprep.ClientID,
Forms!frInvSelect!StartDate
AS Start, Forms!frInvSelect!EndDate AS [End], Forms!frInvSelect!InvDate AS
Inv " & vbCrLf & _
"FROM qry_invprep " & vbCrLf & _
"WHERE qry_invprep.ClientID IN(" & strCriteria & ")" & vbCrLf & _
"GROUP BY qry_invprep.Cust, qry_invprep.ClientID;"

Add the following line just before you build strSQL:

strCriteria = Replace(strCriteria, "'", "''")

(ie replace every single apostrophe with two of them)