Results 1 to 5 of 5
  1. #1
    Bosco_M is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    3

    how to remove what is supposed to be an apostrophe from string "'" in field value using VBA

    Hello,

    I have seen posts on correcting names with apostrophes, however, I have "'" instead of an apostrophe. Which I assume is a character code because if I search for records like "*'*" I have zero records returned. How do I get this code to show as an apostrophe? So it is legible.

    For example,
    COOK'S shows up as COOK'S

    Thank you for your help

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Where are you seeing this code? "'" is what HTML uses for an apostrophe. If you are seeing it in a textbox on a form, is the textbox bound to a memo table field, with the text format property set to RTF?

    What does the text look like if you just open the table containing it?

  3. #3
    Bosco_M is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    3
    Hello John_G

    Thank you for responding back so quickly. It is in the field values. The table in question is a result of a export (csv) from a corporate DB. The csv file is linked into MS Access. In the official name field, some of the names listed have "'" (and later discovered """) instead of an apostrophe. I have since created a loop to go through each Name and replace. The loop contains the following:
    strTemp = Replace(strTemp, "'", "''")
    strTemp = Replace(strTemp, """, "''")
    This seems to be fixing it.... I hope..

    I would be open to suggestions if there is a better method.
    Thank you.

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    I thought it might be something like that. It probably has something to do with the settings used by the export procedure to prevent apostrophes and quotation marks that are part of the data from messing up the syntax in the csv file.

    I think your approach to fixing it is about all you have. I think you will find that ' is for 'single quotes' (apostrophes), and " is for "double-quotes".

  5. #5
    Bosco_M is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2016
    Posts
    3
    Hello John_G,

    Thank you for your help!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 12-20-2015, 12:16 PM
  2. Replies: 4
    Last Post: 07-12-2014, 02:02 PM
  3. Replies: 3
    Last Post: 04-22-2013, 06:08 AM
  4. How Do I Remove "Time" from a Datetime Field
    By James Parker in forum Queries
    Replies: 4
    Last Post: 01-06-2012, 03:05 PM
  5. Replies: 16
    Last Post: 07-22-2011, 09:23 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums