Handle single and double quotations in a string? 1

[ponderdj]

Is it impossible to handle single and double quotations in a string?

PHV brought up a pretty tricky situation in thread705-860086

I have a similar situation and can't find a way to make it work.

I've tried using a combination of the DQ variable and PHV's Replace() function to no avail.

Example: The user enters: she's going to say, "Hello"

Private Sub Cmd_OK_Click()
Dim x As String
Dim xString As String
Dim DQ As String
Dim strTest As String
If IsNull(TB_Comment) Then Exit Sub

On Error GoTo err_handler
xString = TB_Comment
DQ = """"

With Form_Frm_SOF
    If Cmd_OK.Caption = "Add" Then
    
           
    strTest = "INSERT INTO TBL_COMMENTS (MF_ID,COMMENT,IMPORT_DATE,USER) " & _
            "VALUES ('" & .L_MF.Caption & .L_FY.Caption & .L_FM.Caption & "'," & DQ & Replace(xString, "'", "''") & DQ & ",'" & Now() & "','" & CurrentUser() & "');"

    Debug.Print strTest

I'm going to run DoCmd.RunSQL in place of strTest if I can get this working.

 

[Golom]

Just

strTest = "INSERT INTO TBL_COMMENTS (MF_ID,COMMENT,IMPORT_DATE,USER) " & _
"VALUES ('" & .L_MF.Caption & .L_FY.Caption & .L_FM.Caption & _
"','" & Replace(xString, "'", "''") & _
"',#" & Now() & "#,'" & CurrentUser() & "');"

should work, assuming that the double quotes are already in the xString variable.

 

[CautionMP]

ponderdj,
And if that doesn't work you could do some old fashion concatenation:

Private Sub Cmd_OK_Click()
Dim x As String
Dim xString As String
Dim strTest As String
If IsNull(TB_Comment) Then Exit Sub

On Error GoTo err_handler
[b]xString = Replace(TB_Comment, Chr(39), "' & Chr(39) & '")
xString = Replace(xString, Chr(34), "' & Chr(34) & '")[/b]


With Form_Frm_SOF
    If Cmd_OK.Caption = "Add" Then
    
           
    strTest = "INSERT INTO TBL_COMMENTS (MF_ID,COMMENT,IMPORT_DATE,USER) " & _
            "VALUES ('" & .L_MF.Caption & .L_FY.Caption & .L_FM.Caption & "',[b]'[/b]" & xString & "[b]'[/b],'" & Now() & "','" & CurrentUser() & "');"

    Debug.Print strTest

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[PHV]

I second Golom.
No need of DQ nor convoluted double Replace ...