Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Nov 2004
    Location
    London, United Kingdom
    Posts
    152
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Quotation Marks (Access 2000)

    Hi

    I have a piece of code that iterates round a recordset and inserts records to a table, my code is:
    strSQL = "Update [client details TEST]" & Chr(13)
    strSQL = strSQL & "Set forenames = '" & .Fields("forenames") & "', " & Chr(13)
    strSQL = strSQL & "surname = '" & .Fields("surname") & "', " & Chr(13)
    strSQL = strSQL & "WHERE [client details TEST].ni_number='" & strNINO & "';"
    DoCmd.RunSQL strSQL
    The code works fine until one of the names that I have in my recordset is O'Donaghue - the apostrophe interferes with the code so I end up with an sql statement
    Update [client details TEST]
    Set forenames = 'John Paul',
    surname = 'O'Donaghue',
    birth_date = #10/10/1979#,
    admission_date = #05/04/2004#,
    current_status = 'In Home'
    WHERE [client details TEST].ni_number='JW123437B';

    Any help in solving this problem would be much appreciated.

    Amanda

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quotation Marks (Access 2000)

    Assuming that there are no double quotes in your strings, you can use Chr(34) to insert double quotes instead of single quotes into the SQL string.
    When assembling an SQL string in code, it is not necessary to include line feeds or close the string with a semi-colon, so you can change the code to
    <code>
    strSQL = "UPDATE [client details TEST] "
    strSQL = strSQL & "SET forenames = " & Chr(34) & .Fields("forenames") & Chr(34) & ", "
    strSQL = strSQL & "surname = " & Chr(34) & .Fields("surname") & Chr(34) & ", "
    strSQL = strSQL & "WHERE [client details TEST].ni_number=" & Chr(34) & strNINO & Chr(34)
    DoCmd.RunSQL strSQL
    </code>
    Also see Chr() function and quotes within strings (all).

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Leuven, Vlaanderen, Belgium
    Posts
    322
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: Quotation Marks (Access 2000)

    Interesting explanatory post you refer to Hans <img src=/S/smile.gif border=0 alt=smile width=15 height=15>.
    Only for the record: this Microsoft MSDN article helped me out before I found your advise: Quotation Marks in Strings.
    Take care,
    Hasse

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •