Access Cookbook - Ken Getz [140]
The simplest solution is to use Chr$(34) to embed the quotes. An expression such as the following would do the trick:
"[LastName] = " & Chr$(34) & strLastName & Chr$(34)
If you don't believe this works, go to the Immediate window in VBA and type this:
? Chr$(34)
Access will return to you by typing the value of Chr$(34)--a quote character.
To make this solution a little simpler, you could create a string variable at the beginning of your procedure and assign to it the value of Chr$(34):
Dim strQuote As String
Dim strLookup As String
strQuote = Chr$(34)
strLookup = "[LastName] = " & strQuote & strLastName & strQuote
This actually makes the code almost readable!
Finally, if you grow weary of defining that variable in every procedure you write, you might consider using a constant instead. You might be tempted to try this:
Const QUOTE = Chr$(34)
Unfortunately, Access won't allow you to create a constant whose value is an expression. If you want to use a constant, your answer is to rely on the "two-quote" rule:
Const QUOTE = """"
Although this expression's use is not immediately clear, it works just fine. The constant is two quotes (which Access will see as a single quote) inside a quoted string. Using this constant, the previous expression becomes:
strLookup = "[LastName] = " & QUOTE & strLastName & QUOTE
To encapsulate all these rules, you might want to use the acbFixUp function in the basFixUpValue module in 07-01.MDB. This function takes as a parameter a variant value and surrounds it with the appropriate delimiters. Its source code is:
Function acbFixUp(ByVal varValue As Variant) As Variant
' Add the appropriate delimiters, depending on the data type.
' Put quotes around text, #s around dates, and nothing
' around numeric values.
' If you're using equality in your expression, you should
' use Basic's BuildCriteria function instead of calling
' this function.
Const QUOTE = """"
Select Case VarType(varValue)
Case vbInteger, vbSingle, vbDouble, vbLong, vbCurrency
acbFixUp = CStr(varValue)
Case vbString
acbFixUp = QUOTE & varValue & QUOTE
Case vbDate
acbFixUp = "#" & varValue & "#"
Case Else
acbFixUp = Null
End Select
End Function
Once you've included this function in your own application, you can call it, rather than formatting the data yourself. The sample code in frmQuoteTest uses this function. For example, here's how to build the expression from the previous example:
"[LastName] = " & FixUp(strLastName)
abcFixUp will do the work of figuring out the data type and surrounding the data with the necessary delimiters.
TIP
Access also provides a useful function, BuildCriteria, that will accept a field name, a data type, and a field value and will create an expression of this sort:
FieldName = "FieldValue"
with the appropriate delimiters, depending on the data type. We've used this in our example in the case where you uncheck the Use Like checkbox. It won't help if you want an expression that uses wildcards, but if you're looking for an exact match, it does most of the work of inserting the correct delimiters for you. To study the example, look at the BuildWhere function in frmQuoteTest's module.
7.2. Create a Global Procedure Stack
Problem
When you're writing an application, you often need to know the name of the current procedure from within your code. For example, if an error occurs, you'd like to be able to have a generic function handle the error and display the name of the procedure in which the error occurred (and all the procedures that have been called on the way to get there). VBA doesn't include a way to retrieve this information. How can you accomplish this?
Solution
By maintaining a list of active procedures, adding the current name to the list on the way into the procedure and removing it on the way out, you can always keep track of the current procedure and the procedure