The Access Wizard Newsletter
Tips, Tricks, and Traps for Access Users and Developers.

Beware the Apostrophe Trap

Back in the early '60s, a TV show called The Twilight Zone was wildly popular. One of its most memorable episodes was a segment called “To Serve Man.” In that segment, apparently benevolent aliens called the Kanamits came to Earth. They claimed they were here to help humans; and help they did. Wars became something in history books, disease was eliminated, and hunger became unknown. The Kanamits shared a book, with the English title, To Serve Man. Unfortunately, the text of the book itself was in the Kanamit native language. As part of their effort, they enticed a set of humans to return with them to their home planet. A skeptical scientist felt that there was an ulterior motive behind the alien’s apparent benevolence. From the beginning, he tried to understand the book, but was not able to decipher the language.

 

As the humans were entering the alien space ship getting ready to leave the earth, the scientist had finally determined the nature of the book. He ran to the ship shouting  “Don't go! Don't go! The book, To Serve Man – it's a cookbook!”  

The aliens had laid a trap for the humans – one that people willingly walked into. Unfortunately, in Microsoft Access, there are also a series of traps. One of the most common is the apostrophe trap. In this month’s Wizard, we will look at the most common scenarios for this trap and learn how to deal with them.
 

The Problem

The problem with the apostrophe character lies in the languages in the Access background: Visual Basic for Applications (VBA) and Structured Query Language (SQL). Both of these languages use the apostrophe for a text delimiter. Essentially that means that, when one of the languages encounters an apostrophe (as well as a double quote), it assumes that what follows is text and the text will be terminated with a matching apostrophe or double quote. 

In most cases, this works out just fine. However, problems typically come about in one of two ways:

The first is when the program has to interpret last names with apostrophes, like O'Malley or O'Brien or O’Connell. This problem comes to light when a “Person’s Name” drop-down is used in a form to find people within the form.

The second problem occurs when a string containing an apostrophe is manipulated in the VBA code – this one is much more subtle, but easily fixed.
 

The Solution

There are two ways to solve the apostrophe problem. If you are using a combo box that contains names or list elements that may contain an apostrophe, avoid using the text column as the bound field. If you are not sure how to do this, take a look at the following articles in the Wizard Library: September 2005, October 2005, November 2005, and December 2005.  This set of articles provides a tutorial on building combo boxes.
 
The other method is fairly simple. If you are writing VBA code and there is any chance that your code may encounter an embedded apostrophe, replace every instance of the apostrophe with a double apostrophe. This sounds much more complicated than it really is.
 
Let's say you have function of that takes an incoming variable called “strIn”.

To have this function handle the potential apostrophe appropriately, simply add the following line to the beginning of your function
 
strIn = Replace(strIn, "'", "''") 
 
This technique uses the built in VBA Replace function. The particular information here takes the variable strIn, looks for an apostrophe and then substitutes that apostrophe with two apostrophes. From there, the existence of the apostrophe will not cause your code to fail.
 

Conclusion

Once you get beyond simple applications, the opportunities to fall into traps will surround you. The ones that I have highlighted here are the most common. Using these techniques, you will avoid falling into these particular traps.

 

Access Developer Videos Now Available
 

A wise man once said that a picture is worth 1,000 words. If a picture is worth 1,000 words then a video can be worth 100,000 words. In an effort to make it easier for people to learn some the techniques that I have covered over the years in the Access Wizard, I’m putting together a library of videos called “Access Developer Tips and Tricks.”

These videos will show how to do something that is much easier to understand with a video than with words and static pictures. Although right now the library is sparse, over time it will grow. Each month, my main webpage will contain a highlighted video on the opening page: http://custom-software.biz/. This month’s video is Access Queries in Total.

The entire video library is at Access Developer Videos link, http://custom-software.biz/accessdevvideo.html.

I am excited about this new tool for helping people to learn Access. Any suggestions you have, I will warmly welcome.

 

Quick Links:

Custom Software Home

Access Wizard Archives

Our Services


© Copyright Custom Software
All Rights Reserved | Westford, MA 01886