' in SQL QUERY

[24sharon]

I have a problem with ', and get en error if the name is with '
( I dont know the name of ' )

what the best to do?
1. replace the ' with 2 ' ('')
2. dont let to write '
3. replace with chr


?

thanks, sharon

 

[LaBelette]

Function to deal with apostrophes in SQL

I wrote this function to deal with this problem:

Public Function CSQL(strSQL As String) As String
    If strSQL = "*" Then
        ' When its "*", just return "*"
        CSQL = "*"
    Else
        ' Double the apostrophes and add two apostrophes, one at the beginning, one at the end
        CSQL = "'" & Replace(strSQL, "'", "''") & "'"
    End If
End Function

... and I call it like this :

varValue = DLookup("[myField]", "myTable", "[myTextField]=" & CSQL(strSomeStringVariable))

Hope it works, worked for me!

 

[24sharon]

Sorry

but i didnt understand where am i write it.

I attach the error and the VBA that do the error, and may you understand more...

 

[mresann]

I'm not sure, but could there be Unicode issues involved here?

 

[FoFa]

You just need to create the function in a module.
Then change your code to "wrap" your variables around the function.

" VALUES (" & rs!kod_horaa & ",'" & CSQL(rs![yadid.yadid_kod]) & "'," etc.
Using a part of your example

 

[LaBelette]

I'm not sure, but could there be Unicode issues involved here?

I don't think so, it just look like a apostrophes problem.

Please, post the whole line starting with:
strSQL = "INSERT INTO ...
I just see a bit of it in your screenshot. Please copy/Paste it so i can correct it without having to type it all over. (I'm very lazy...)

 

[LaBelette]

You just need to create the function in a module.
Then change your code to "wrap" your variables around the function.

" VALUES (" & rs!kod_horaa & ",'" & CSQL(rs![yadid.yadid_kod]) & "'," etc.

Using a part of your example

In fact its:
" VALUES (" & rs!kod_horaa & ", " & CSQL(rs![yadid.yadid_kod]) & " ," etc.

The CSQL function adds apostrophes at the beginning and at the end of the string. I found it more easy this way.

 

[24sharon]

Thanks

I do like it, and it work, thanks

 [B]nameYadid1 = Replace(rs!nameYadid, "'", "''")[/B]  
 strSQL = "INSERT INTO zmaniMASAV(kod_horaa, yadid_kod, nameYadid ,tz, MySum, coin, sum2, bank, snif, cheshbon, chiyuv_date)" _
    & " VALUES (" & rs!kod_horaa & "," & rs![yadid.yadid_kod] & ",'" & [B]nameYadid1[/B] & "','" & rs!yadid_tz & "'," & rs!MySum & "," & rs!coin & "," & sum2 & ",'" & rs!bank & "','" & rs!snif & "','" & rs!cheshbon & "'," & rs!chiyuv_day & ")"
    CurrentDb.Execute (strSQL)