Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Remove a character from a string (Access 2000)

    My question is how do you remove a speech mark (") from the beginning and end of a field in a table. Not all values stored have the " though.

    Any suggestion appreciated.

    Many thanks

    David

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Remove a character from a string (Access 2000)

    Run the following SQL statement, it will remove a single occurance of double quotes ( chr(34) ), so you may have to run the query multiple times.

    UPDATE tablename
    SET field1 = iif(instr(1,field1,chr(34),2)=0,field1,left(field1 ,instr(1,field1,chr(34),2)-1) & right(field1,len(field1)-instr(1,field1,chr(34),2)));

    You will have to change field1 to your fieldname and change the table name to your table name.
    HTH
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  3. #3
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Remove a character from a string (Access 2000)

    If the quotes appear ONLY at beginning or end of field you can use following function to trim quote marks:

    <pre>Public Function TrimQuotes(strText As String) As String

    If Left(strText, 1) = Chr(34) Then
    strText = Right(strText, Len(strText) - 1)
    End If
    If Right(strText, 1) = Chr(34) Then
    strText = Left(strText, Len(strText) - 1)
    End If

    TrimQuotes = strText

    End Function
    </pre>

    You can use this function in an update query to update table. If not all records have data in this field be sure to include "Is Not Null" in the update query criteria line.

    Other, "manual", option: Open table in datasheet view, select column, open Find & Replace (CTRL + H), in "Find What" box enter ", leave "Replace With" blank, in "Match" specify "Any Part of Field", then click "Replace All" button. This will get rid of all quotes in selected field, regardless of where the quotes are located.

    HTH

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Remove a character from a string (Access 2000)

    Hi,
    You could create your own function like this:
    <pre>Function RemoveQuotes(strInput As String) As String
    RemoveQuotes = Replace(strInput, """", "")
    End Function
    </pre>

    and then use it in an update query to update your field (e.g. Field1) to removequotes([Field1])
    Note: If any of your fields are null, you'll need to amend the code to handle this - something like:
    <pre>Function RemoveQuotes(strInput)
    If Not IsNull(strInput) Then
    RemoveQuotes = Replace(strInput, """", "")
    End If
    End Function
    </pre>

    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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