Both users and developers at times use Quotation Marks while working with Access database. In this article we will learn such use case scenarios in detail.
MS Access developers have regularly used Quotation marks for several purposes including most notably in SQL statements. Let look at some key situations where you need to use Quotation Marks:
- When a user wants to specify criteria for any domain aggregate function.
- When specifying or using criteria for Find method.
- When the user is specifying a query for the Filter or ServerFilter property in a form.
- While building coding or SQL strings.
In order to operate through any of these instances, MS Access needs to pass a string to its database system. When a user specifies his/her criteria argument for an aggregate function, Microsoft Access follows up the string and evaluates the new variables by concatenating them in a new string then passes data from its engine.
The string delimiters or quotes are not a part of the variable; however, they are essential to be included in a string, as they help Access in reading the human language. String Construction can be determined in three different Categories.
- Single Quotation Marks
- Double Quotation Marks
- Variable Representative Quotation Marks
How to Properly Insert and Use Quotation Marks?
Basics
Users can use a single-quote character in some contexts by inserting single (‘) quotes within (“) quotes. However, most IT experts avoid using it; as it fails to work as soon as the string or name contains any unreadable sign or an apostrophe.
- Users are not allowed to insert quotes inside quotes like:
= “Here is the “subject” key in quotes” (Error)
Access is designed to read the text as the quote ends till ‘subject’, which makes it look like the end of the string, leaving it clueless to operate with the remaining characters in the field.
- The conventional way to enter the data is by using double quotes for example:
= “Here is the ““subject”” key in quotes”
Access reads literal text according to the quotes; however, when using multiple quotes within a quote, it is unable to understand the end of the sentence and assumes that it has already ended at ‘subject’.
- User might find it weird, as the string is ending with three quotes (double-up + the closing quote)
= “Here is the ““subject”””
However, when a user uses double-up quotes, Access is able to read its characters inside the quotes.
Expressions
Quotation marks are usually used with expressions, to express the meaning of any given string.
- For example, if a user looks in the X database, for a city of the customer sitting on the table where the CompanyName is “ABC”, you will create the following string:
=DLookup ("City", "Customers", "CompanyName = ""ABC""")
- But if the user wants to look for the city of the customer sitting on the table in the form, he/she would have to create a different string.
=DLookup("City", "Customers", "CompanyName = """ & [CompanyName] & """")
This string consists of 3 continuous quotes in the middle and 4 at the end. The opening literal text of the string is single quoted whereas the sentence processed after the quote character it is being double since the previous categories are under quotes.
If you use MS Access for running important office applications or perform line of business functions, you may well consider keeping a tool handy to repair Access and deal with contingencies like a database crash.
Author Introduction:
Vivian Stevens is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including recover mdf and excel recovery software products. For more information visit www.datanumen.com
Leave a Reply