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.
