Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2010

    Unanswered: Best way to deal with apostrophes

    What is the best way in VBA for an access database to deal with apostrophes?

  2. #2
    Join Date
    Oct 2010
    Replace them with 2 apostrophes?

    In all actuality though it depends on the process. You wouldn't store Bob''s in a database table. But if you ever have a search field or a query that contains user input there is always that chance that the user is going to put some data with an apostrophe. This happens most often in memo fields and text fields where notes are kept or names of businesses.

    Replace(str, "'", "''")
    Chr(39) returns ' - apostrphe
    chr(34) returns " - double quote

  3. #3
    Join Date
    Feb 2004
    Chicago, IL
    Apostrophes in data? Where are you running into trouble? If you are trying to create a string that has embedded apostrophes then using double quotes around the text should be fine.

    Apostrophes should pose any real problem in data. There are ways to work with them. I certainly wouldn't recommend replacing apostrophe s within the data with anything else.

  4. #4
    Join Date
    Dec 2002
    Préverenges, Switzerland
    a possible cheat

    replace ' (U+0027 APOSTROPHE = chr(39)) in "Bob's cat"
    ...with ´ (U+00B4 ACCENT AIGU = chr(180)) to get "Bob´s cat"
    or with ` (U+0060 ACCENT GRAVE = chr(96)) to get "Bob`s cat"

    both print reasonably OK for reports etc
    if you do a similar replace in user-input search strings it works fine.

    Bob´s cat
    Bob`s cat

    Bob´s cat
    Bob`s cat

    ...and if they look too odd for your taste, replace in reverse for reports etc
    currently using SS 2008R2

Posting Permissions

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