VBA- how to use' ' correctly

[Jana.Luo]

Hi there,

Part of the script like this below. The values in some columns are varchar. But this script works fine until it meets a name like this KK K'mm.

How can I modify the script to avoid this problem?

====================================

Sub Test1()
Dim SQL As String
Dim X As Integer

-------------------------
C.Execute "Truncate Table Agent_list"

For X = 2 To 142


SQL = "Insert Into Agent_list Values ( '" & Cells(X, 1) & "', "


If Cells(X, 2) = "" Then
SQL = SQL & "Null, "
Else
SQL = SQL & "'" & Cells(X, 2) & "', "
End If
---------------------------


C.Execute SQL

Next X

============================

many thanks,
Jana

 

[Jana.Luo]

the message box came out :

Run-time error 'xxxxxxxxxx)':
Incorrect syntax near')'.

and when my cursor over the

C.Execute SQL
the message was :

SQL="Flase'AKL','XXX')"

I think the result should be "('AKL','XXX')" , right?

 

[xenou]

I guess so, but even that is only part of a sql statement. You seem to be not constructing the proper sql command text. I'd start with sample data that doesn't have single quotes in them, then when that's working move up to the data that has the single quotes in it. It also helps (if you can) to be able to test the raw sql directly on your database (such as in SQL Server Management Studio, in an Access query, or at a MySQL prompt or Oracle prompt).

 

[Jana.Luo]

I have got it correct!

Thanks a lot for helping me out Xenou!

Regards,
Jana