Double Quotes and SQL

[mrgreen]

I have an Insert Into Query and an Update Query that will bomb out if the user enters double quotes. I was thinking the syntax was
Function RemoveCharacters(x As String) As String
RemoveCharacters = Replace(x, """", "'")

Obviously this isn't working as I planned. Any information would be great!

 

[SOS]

What's your SQL String look like? Perhaps we can use an alternative method.

 

[rainman89]

Isn't it this?

Replace(x,' " ',' ' ' ')

I'm most likely wrong, but feel I like I've done this before

 

[rainman89]

Or maybe??

Replace(x, Chr(34), Chr(39))

Stole that one from your other post SOS!

 

[mrgreen]

Thanks for such a quick reply!

Here is a snippet of the code
stryieldroot = Nz(.txtyieldrootcause.Value)
stryieldroot = RemoveCharacters(stryieldroot)
stryieldca = Nz(.txtyieldcorrectiveaction.Value)
stryieldca = RemoveCharacters(stryieldca)





Here is my function to remove characters (I thought)
Function RemoveCharacters(x As String) As String
RemoveCharacters = Replace(x, """", "''")
RemoveCharacters = Replace(x, "+", "")
RemoveCharacters = Replace(x, "/", "")
RemoveCharacters = Replace(x, ",", "")
RemoveCharacters = Replace(x, "&", "and")
End Function




strSQL = "UPDATE tblrootcausehistory SET ROOTCAUSEYIELD=""" & stryieldroot & ""","
strSQL = strSQL & "CORRECTIVEACTIONYIELD=""" & stryieldca & ""","
strSQL = strSQL & "VARREVYIELD='" & strrevyield & "',"
strSQL = strSQL & "ROOTCAUSESCRAP=""" & strscraproot & ""","
strSQL = strSQL & "CORRECTIVEACTIONSCRAP=""" & strscrapca & ""","
strSQL = strSQL & "VARREVSCRAP='" & strscraprev & "',"
strSQL = strSQL & "ROOTCAUSETOOL=""" & strtoolroot & ""","
strSQL = strSQL & "CORRECTIVEACTIONTOOL=""" & strtoolca & ""","
strSQL = strSQL & "VARREVTOOL='" & strtoolrev & "',"
strSQL = strSQL & "ROOTCAUSEDOWNTIME=""" & strdtroot & ""","
strSQL = strSQL & "CORRECTIVEACTIONDOWNTIME=""" & strdtca & ""","
strSQL = strSQL & "VARREVDOWNTIME='" & strrevdt & "',"
strSQL = strSQL & "ROOTCAUSECASES=""" & strcasesroot & ""","
strSQL = strSQL & "CORRECTIVEACTIONCASES=""" & strrcasesca & ""","
strSQL = strSQL & "VARREVCASES='" & strrevcases & "'"
strSQL = strSQL & " WHERE(ENTRYID=" & lngentryid & ");"

 

[mrgreen]

Hi All,

Again thanks for the assistance.

I tried all the above mentioned and it didn't work. I'llput the replace function in the SQL statement and hopefully that will work. I just thought this would be cleaner.

 

[mrgreen]

Well I got it to work. I ended up doing this...

Function RemoveCharacters(x As String) As String
x = Replace(x, """", "''")
x = Replace(x, "+", " plus ")
x = Replace(x, "/", " ")
x = Replace(x, ",", " ")
x = Replace(x, "&", " and ")
RemoveCharacters = x
End Function

Instead of this... drrrrrr

Function RemoveCharacters(x As String) As String
RemoveCharacters = Replace(x, """", "''")
RemoveCharacters = Replace(x, "+", "")
RemoveCharacters = Replace(x, "/", "")
RemoveCharacters = Replace(x, ",", "")
RemoveCharacters = Replace(x, "&", "and")
End Function


I guess I'll learn how to do this some day!

Thanks for the tips!