Single and double quotes in a INSERT statement

[dccjr]

I am using an INSERT statement to write several fields to a table from an unbound form. See below

CurrentDb.Execute "INSERT INTO RCAData1 (AreaCell, Employee, DefectDate, PartNo, WorkOrderNo, QualityNo, Description, Disposition, Notes, ImmediateAction, ContainmentAction, Why1, Why2, Why3, Why4, Why5, Additional, Category, Type, RootCause, ActionPlan, ActionsTaken, AssignedTo, DueDate, Status, Comments, CompletedDate)" _
& "VALUES ('" & tbxAreaCell & "','" & tbxEmployee.Value & "',#" & Format(tbxDefectDate.Value, "mm/dd/yyyy") & "#,'" & tbxPartNo & "','" & tbxWONo & "','" & tbxQualityNo & "','" & tbxDefectDescription & "','" & tbxDisposition & "','" & tbxNotes & "','" & tbxImmediateAction & "','" & tbxContainment & "','" & tbxWhy1 & "','" & tbxWhy2 & "','" & tbxWhy3 & "','" & tbxWhy4 & " ','" & tbxWhy5 & "','" & tbxWhyAdditional & "','" & cboCategory & "','" & cboType & "','" & tbxRootCause & "','" & tbxActionPlan & "','" & tbxActionTaken & "','" & tbxAssignedTo & "',#" & Format(tbxDueDate.Value, "mm/dd/yyyy") & "#,'" & tbxStatus & "','" & tbxComments & "',#" & Format(tbxCompletedDate.Value, "mm/dd/yyyy") & "#)", dbFailOnError

I am having and issue with some of the tbx's having data with a single quote and some that have (or could have) a direct quote from another person. I have search and read several pieces on dealing with this issue, however they all seem to gear toward one or the other. I cannot find any that deal with both, much less instances where it could have one or both of these, but might not.

Is there any easy way to deal with these two delimiters when and if they occur on the fly? I am just not sure how to combine what was covered in other posts and articles.

Many thanks.

[June7]

I deal with single quote with Replace(textbox, "'", "''").

How have you seen double quote dealt with?

I absolutely FORBID my users to use double quote in data.

[dr91075]

How about something like this:

Sub Test()
Dim str As String, isQl As String, strSingle As String, strDouble As String, RS As Recordset
strSingle = Chr(39)
strDouble = Chr(34)
str = Text0
str = Replace(str, strSingle, "****")
str = Replace(str, strDouble, "####")
isQl = "INSERT INTO Table1(field1) values('" & str & "');"
CurrentDb.Execute isQl
Set RS = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)
With RS
    Do While Not .EOF
        If !field1 = str Then
            .Edit
            !field1 = Replace(!field1, "****", strSingle)
            !field1 = Replace(!field1, "####", strDouble)
            .Update
            Exit Do
        End If
        .MoveNext
    Loop
    .Close
End With
End Sub

It's not pretty and I'm sure someone else could come up with something better, but it works.

EDIT: You can remove the loop and just add .MoveLast since it will be the last record.

With RS
    .MoveLast
        If !field1 = str Then
            .Edit
            !field1 = Replace(!field1, "****", strSingle)
            !field1 = Replace(!field1, "####", strDouble)
            .Update
        End If
    .Close
End With

[dccjr]

I am not sure if any of this really helped. I tried replacing the above INSERT line with the following. Now it won't even execute. I get a Runtime Error 3920.

CurrentDb.Execute "INSERT INTO RCAData1 (AreaCell, Employee, DefectDate, PartNo, WorkOrderNo, QualityNo, Description, Disposition, Notes, ImmediateAction, ContainmentAction, Why1, Why2, Why3, Why4, Why5, Additional, Category, Type, RootCause, ActionPlan, ActionsTaken, AssignedTo, DueDate, Status, Comments, CompletedDate)" _
                    & "VALUES (""" & tbxAreaCell.Value & """,""" & tbxEmployee.Value & """,#" & Format(tbxDefectDate.Value, "mm/dd/yyyy") & "#,""" & tbxPartNo & """,""" & tbxWONo & """,""" & tbxQualityNo & """,""" & tbxDefectDescription & """,""" & tbxDisposition & """,""" & tbxNotes & """,""" & tbxImmediateAction & """,""" & tbxContainment & """,""" & tbxWhy1 & """,""" & tbxWhy2 & """,""" & tbxWhy3 & """,""" & tbxWhy4 & """,""" & tbxWhy5 & """,""" & tbxWhyAdditional & """,""" & cboCategory & """,""" & cboType & """,""" & tbxRootCause & """,""" & tbxActionPlan & """,""" & tbxActionTaken & """,""" & tbxAssignedTo & """,#" & Format(tbxDueDate.Value, "mm/dd/yyyy") & "#,""" & cboStatus & """,""" & tbxComments & """,#" & Format(tbxCompletedDate.Value, "mm/dd/yyyy") & "#)", dbFailOnError

I would not even worry about it except I have employees with apostrophes and possessives in some fields. I have decided to not allow double quotes at all. I am sure that I have missed something in this case. MS Help was anything but helpful (surprise?). All I got was that it was an error with a dll file, but how can that be the issue? It wasn't an issue when I was using the single quote in the INSERT line.

Comments? Suggestions?

[June7]

Need to use Replace as I show in post 2 on any variable where apostrophe is possible.

Replace(tbxComments, "'", "''")

[dr91075]

Did you try replacing? The code I posted was just an example showing what could be done. Essentially, you can pass the string to a variable,replace the "" and ' with something else (preferably something that wouldn't be entered by a user), run the INSERT statement using the variable, open a recordset of the table, move to the last record, and replace whatever you used with the "" or ' using the chr function.

[dccjr]

dr91075, I am trying to adapt you replace code. Since I have multiple fields that could have single or double quotes, I am looping through each control with the REPLACE. I have hit a stumbling block, however. I am not sure how to look through the fields in the table to change them back to quotes. Below is my current attempt at adapting you code, but it is incomplete and I am not sure how to fix it. Any ideas (or anyone else for that matter).

'Replaces single quote with ~~~~ and double quote with ^^^^ in each control
    For Each replaceCtrl In frmDataForm.Controls
        strSingle = Chr(39)
        strDouble = Chr(34)
            Select Case TypeName(replaceCtrl)
                Case "Textbox"
                    str = replaceCtrl.Value
                    str = Replace(str, strSingle, "~~~~")
                    str = Replace(str, strDouble, "^^^^")
                Case "ComboBox"
                    str =
    Next replaceCtrl
'Inserts record into table
InsStr = "INSERT INTO RCAData1 VALUES ('" & tbxAreaCell & "','" & tbxEmployee & "',#" & Format(tbxDefectDate, "mm/dd/yyyy") & "#,'" & tbxPartNo & "','" & tbxWONo & "','" & tbxQualityNo & "','" & tbxDefectDescription & "','" & tbxDisposition & "','" & tbxNotes & "','" & tbxImmediateAction & "','" & tbxContainment & "','" & tbxWhy1 & "','" & tbxWhy2 & "','" & tbxWhy3 & "','" & tbxWhy4 & "','" & tbxWhy5 & "','" & tbxWhyAdditional & "','" & cboCategory & "','" & cboType & "','" & tbxRootCause & "','" & tbxActionPlan & "','" & tbxActionTaken & "','" & tbxAssignedTo & "',#" & Format(tbxDueDate, "mm/dd/yyyy") & "#,'" & cboStatus & "','" & tbxComments & "',#" & Format(tbxCompletedDate, "mm/dd/yyyy") & "#);"
CurrentDb.Execute InsStr
'Opens recordset and changes inserted symbols back to correct quotes
    Set RS = CurrentDb.OpenRecordset("RCAData1", dbOpenDynaset)
    With RS
        .MoveLast
            If !field1 = str Then
                .Edit
                !field1 = Replace(!field1, "****", strSingle)
                !field1 = Replace(!field1, "####", strDouble)
                .Update
            End If
        .Close
    End With

Thakns for all of the help.

[June7]

dccjr, there is no need to replace the apostrophe with other characters in the table. Just do the Replace in the sql statement as I show. It doesn't actually put 2 apostrophes in the data, it only forces the sql to recognize the apostrophe as a printable character and not just an sql delimiter. I don't know how to force quote marks therefore I forbid their use in data. If I didn't use so much SQL in VBA would not be an issue. Access query objects don't seem to have issue with apostrophes and quotes in data, it is only when concatenating and running SQL in VBA that becomes a problem.

[dr91075]

Assign a variable to each control value. Test that value for the existence of the character or characters that are causing you trouble using the Instr function. Use the variables instead of the controls in the INSERT statement. I'll look at your code and post an example a little later. I have to say I disagree with June on this one. The single quote can indeed cause trouble when using sql if you're not careful. I'm not saying June is wrong. I have great respect, but I just think it safer to test for both.

[June7]

Talking about 2 approaches here. SQL in VBA vs Access query objects.

I know the single quote (apostrophe) as well as quote mark in data is an issue with VBA SQL. That's why I use the Replace function when I build SQL in VBA and no need to actually modify the data in the table. However, my attempts to handle the quote mark with Replace when building SQL always failed, hence the ban on quote marks in data.

I have tested Access query objects on data containing apostrophes and quote marks. The issue did not present. The Access action queries (UPDATE, INSERT) ran fine.

[dccjr]

June7, you use the Replace(tbx...) in the actual INSERT SQL statement, in place of just the tbx name?

[dr91075]

I agree with you there, June. However, I don't like forbidding users to enter data that, in this case, seems crucial. It's the job of the programmar to account for that, in my opinion.

dccjr, your replace code looks fine, except you should add .movenext after the end if line. It looks as if you're only looking at one record.

[dr91075]

I just realized I screwed up the last comment. lol! No movenext is needed. You ARE just looking at one record. Sorry about that.

[dr91075]

Perhaps this will help...

Sub Test()
Dim strAreaCell As String, strEmployee As String
Dim strSingle As String, strDouble As String, strRepSingle As String, strRepDouble As String
Dim iSQL As String, cenSQL As String, endSQL As String
Dim DB As Database, RS As Recordset

Set DB = CurrentDb

strSingle = Chr(39)
strDouble = Chr(34)
strRepSingle = "^^^^"
strRepDouble = "~~~~"
cenSQL = "','"
endSQL = "');"

strAreaCell = Replace(tbxAreaCell, strSingle, strRepSingle): strAreaCell = Replace(strAreaCell, strDouble, strRepDouble)
strEmployee = Replace(tbxemployee, strSingle, strRepSingle): strEmployee = Replace(strEmployee, strDouble, strRepDouble)

iSQL = "INSERT INTO RCAData1 (AreCell,Employee) VALUES('" & strAreaCell & cenSQL & strEmployee & endSQL
CurrentDb.Execute iSQL

Set RS = DB.OpenRecordset("RCAData1", dbOpenDynaset)
With RS
    .MoveLast
    .Edit
    !AreaCell = Replace(!AreaCell, strRepSingle, strSingle): !AreaCell = Replace(!AreaCell, strRepDouble, strDouble)
    !Employee = Replace(!Employee, strRepSingle, strSingle): !Employee = Replace(!Employee, strRepDouble, strDouble)
    .Update
End With
Set RS = Nothing
Set DB = Nothing
End Sub

It's only two of your fields, but it shows the idea.

[June7]

I agree about programmer should handle but at the time I was relatively new to Access and VBA and simply had to get something working. The restriction on quote mark hasn't been an issue for my users but if there is a way to handle I might revisit. Thanks for the idea.