What the rule is with ' ?

[raghuprabhu]

Hi All,
Attached is a small database. I have the following problem. I am trying to save a copy of the records before changing the various fields. To do that I click the "Copy?”and then I click cmdCopy button. I cannot copy if there is an “ ‘ “ in the string of the field.
For example I can copy "Prabhu" but I cannot copy "O’Keefe". There is no problem with "O"Shea". What the rule is with ' (Apostrophe)?

 

[JANR]

To get around this you must double up the quotes like ""FieldName"".

JR

 

[Rabbie]

The name "O"Shea" is not a valid format so you don't have to cater for it. The name would always be written with a single quote so that is what you need to handle.

This code does the trick for you I tested it and it works!

[/SIZE]
[SIZE=3][/SIZE] 
Private Sub cmdCopy_Click()
On Error GoTo ErrorHandler
Dim conn As ADODB.Connection
Dim i As Integer
Dim s As String
Dim sSQL As String
sSQL = "INSERT INTO tbl_Copy ( txt_GivenNames, txt_Surname, dt_DateOfBirth) " _
    & "VALUES (" & Chr(34) & txt_GivenNames & Chr(34) & "," & Chr(34) & txt_Surname & Chr(34) & ", '" & dt_DateOfBirth & "');"
Debug.Print sSQL
Set conn = CurrentProject.Connection
conn.Execute sSQL
Me.bln_Copy = False
GoTo ThatsIt
ErrorHandler:
    Select Case Err.Number
        Case -2147217908 'command text not set
        Case -2147217865 'cannot find table
        'Case -2147217913 'data mismatch
        Case 3021 'no records
        Case Else
            MsgBox "Problem with cmdHasChanged_Click()" & vbCrLf _
                 & "Error " & Err.Number & ": " & Err.Description
    End Select
ThatsIt:
conn.Close
End Sub

Good luck.

 

[Atomic Shrimp]

The problem is that the apostrophe is breaking one of your values early - it creates this query:

INSERT INTO tbl_Copy ( txt_GivenNames, txt_Surname, dt_DateOfBirth) VALUES ( 'Dennis','O'Keefe', '14/05/1949');

-so as far as the interpreter is concerned, the second value in the VALUES list is 'O' - and after that, there's an unexpected expression starting with K

Replace your SQL construction statement with:

sSQL = "INSERT INTO tbl_Copy ( txt_GivenNames, txt_Surname, dt_DateOfBirth) " _
    & "VALUES ( """ & txt_GivenNames & """,""" & txt_Surname & """,""" & dt_DateOfBirth & """);"

and it will build this query:
INSERT INTO tbl_Copy ( txt_GivenNames, txt_Surname, dt_DateOfBirth) VALUES ( "Dennis","O'Keefe","14/05/1949");

(which works)

 

[raghuprabhu]

Thanks JANR, Rabbie and Mike. Your simple solutions have been very helpful.

Cheers
Raghu

 

[Rabbie]

Happy to have been of help.