Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Posts
    11

    Unanswered: using an apostrophe within a variable

    I have this code, which works great until the user enters a name with an apostrophe for NameTxt. I tried to play around with this using double quotes inside of sing qoutes...etc. But can't get anything to work - any help would be greatly appreciated. Thanks!

    sSQL = "Insert into GDSN_TRADING_PARTNER (IP_GLN, NAME, TP_DATE, TP_USER, TARGET_MK) " & _
    "values ( '" & IP_GLN_TMP & "', '" & NameTxt & "', '" & Cur_date & " ', " & _
    " '" & User & "', '" & tm & "' )"

    DoCmd.RunSQL sSQL

  2. #2
    Join Date
    Jan 2004
    Location
    Lancashire, UK
    Posts
    33
    Use this function to replace the apostrophe with double apostrophe.

    Function FindAndReplace(ByVal strInString As String, _
    strFindString As String, _
    strReplaceString As String) As String
    Dim intPtr As Integer
    If Len(strFindString) > 0 Then 'catch if try to find empty string
    Do
    intPtr = InStr(strInString, strFindString)
    If intPtr > 0 Then
    FindAndReplace = FindAndReplace & Left(strInString, intPtr - 1) & _
    strReplaceString
    strInString = Mid(strInString, intPtr + Len(strFindString))
    End If
    Loop While intPtr > 0
    End If
    FindAndReplace = FindAndReplace & strInString
    End Function

    This was taken from the Access Web (http://www.mvps.org/access/index.htm), hope it helps.

  3. #3
    Join Date
    Jun 2004
    Posts
    11
    Thank you - I also found the following line to replace the single quote with 2 single quotes.
    vName = Replace(Me.NameTxt, "'", "''")

  4. #4
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    Is removing the apostrophe an acceptable solution? What about in the rare case that the name includes an apostrophe.

  5. #5
    Join Date
    Nov 2003
    Location
    Sussex, England
    Posts
    404
    Have you tried the old square bracket standby:


    sSQL = "Insert into GDSN_TRADING_PARTNER (IP_GLN, NAME, TP_DATE, TP_USER, TARGET_MK) " & _
    "values ( '" & IP_GLN_TMP & "', '" & [NameTxt] & "', '" & Cur_date & " ', " & _
    " '" & User & "', '" & tm & "' )"

    They sometimes work. I understand the problem - I had a huge patient database report fal iver because one person had an address like Fre'ds Cottage - took me ages to sort it out.

  6. #6
    Join Date
    Jan 2004
    Location
    Lancashire, UK
    Posts
    33
    I've only got Access 97, the replace function's not in that. I know it's in VB, which I use more now.

    Would square brackets work around a field value - not name - like shown?

    Also, the apostrophe is not being removed, it is being doubled up. The stored data will only have 1 apostrophe in it, so Mr O'Brien will not be Mr OBrien.

Posting Permissions

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