String sql problem

[Piter]

Hi
I build string sql in this way:

For Each varItem In Me!Lstclient.It emsSelected
strCriteria = strCriteria & "tbltable1.clie nt = " & Chr(34)
_
& Me!Lstclient.It emData(varItem) & Chr(34) &
"OR "
Next varItem

strSQL = "SELECT * FROM tbltable1 " & _
"WHERE " & strCriteria & ";"

Unfortunately when the name of client is with "" ( for example "SONY" )
it causes error.
I tryied in many ways with "'" but cannt to handle with this problem.
Maybe someone can give me a little help.
Thanks

Piter

[Anthony England]

Re: String sql problem


"Piter" <tod4@wp.pl> wrote in message
news:1142281475 .309389.235760@ i39g2000cwa.goo glegroups.com.. .[color=blue]
> Hi
> I build string sql in this way:
>
> For Each varItem In Me!Lstclient.It emsSelected
> strCriteria = strCriteria & "tbltable1.clie nt = " & Chr(34)
> _
> & Me!Lstclient.It emData(varItem) & Chr(34) &
> "OR "
> Next varItem
>
> strSQL = "SELECT * FROM tbltable1 " & _
> "WHERE " & strCriteria & ";"
>
> Unfortunately when the name of client is with "" ( for example "SONY" )
> it causes error.
> I tryied in many ways with "'" but cannt to handle with this problem.
> Maybe someone can give me a little help.
> Thanks
>
> Piter[/color]


Strangely enough, I answered an almost identical question just a couple of
minutes ago. Something like this should do it:


Private Sub DoTest()

On Error GoTo Err_Handler

Dim strValue As String
Dim strWhere As String
Dim varSelected As Variant
Dim strSQL As String

With Me.lstClient

For Each varSelected In .ItemsSelected
strValue = CStr(.Column(0, varSelected))
strValue = Replace(strValu e, """", """""")
strWhere = strWhere & ",""" & strValue & """"
Next varSelected

End With

If Len(strWhere) > 0 Then
strWhere = Mid$(strWhere, 2)
strWhere = " WHERE Client (" & strWhere & ")"
End If

strSQL="SELECT * FROM tbltable1" & strWhere


Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description , vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub