Domain Functions Demystified: Criteria Expressions

In my last post: Domain Functions Demystified: Introduction, I discussed the structure and syntax of the domain functions in Microsoft Access. The basic syntax is as follows:
DFunctionName("<Fieldname >", "<RecordSource>", "<Criteria Expression>")

• Fieldname refers to the field against which the function will be applied.
• RecordSource refers to a table or query from which the records will be pulled.
• Criteria Expression is basically an SQL Where clause without the WHERE keyword. (optional)

Criteria Expressions

The third argument, the Criteria expression, is the most confusing, so it might be useful to take a closer look at them.
A Criteria Expression is an SQL Where clause without the "WHERE" keyword. Just as in a Where clause, sometimes the value of the expression needs to be delimited, sometimes it doesn't. What determines the delimiter (or lack of one) is the data type of the field.

Numeric

Numeric fields don't need a delimiter. So we can use:
"[OrderNum] = 1"
for the criteria expression. (Although remember that the entire expression must be enclosed in quotes.) Substituting a variable (in this case the name of a bound textbox on a form) for the value yields this:
"[OrderNum] = " & Me.txtOrderID)

Dates

Date fields require the date delimiter. In Access, that's the pound sign or hash mark (#). So with a hard-coded value, my criteria might look something like this:
"[OrderDate] = #1/1/2010#"
With a variable, it would look like this:
"[OrderDate] = #" & Me.txtOrderDate) & "#"

Strings

String or text values are the trickiest type and cause the most confusion. String values must have string delimiters: either quote marks (") or apostrophe ('), but since the entire argument must also be encased in string delimiters you have to somehow tell the expression evaluator which string is which. So suppose I want to use CustName in my domain function.
    [CustName] = "Roger Carlson"
So if I put quotes around the whole thing, I get
"[CustName] = "Roger Carlson""
This will cause an error, however, because of the way the interpreter reads the quotes. In order to put a quote within a quoted string, you have to double the quotes:
"[CustName] = ""Roger Carlson"""
So now, I need to replace the explicit Roger Carlson with the variable:
"[CustName] = "" & Me.txtCustNum & """
But this will also cause an error because I now have two different strings that have to be concatenated and the interpreter isn't reading the quotes right again. To fix it, I have to double the inner quotes again:
"[CustName] = """ & Me.txtCustNum & """"
Now, I said you can also do this with apostrophes. So let me repeat the process:
[CustName] = 'Roger Carlson'
can be fixed surrounded with quotes this way and it will not error.
"[CustName] = 'Roger Carlson'"
But adding the variable again:
"[CustName] = ' & Me.txtCustNum & '"
gives us two incomplete strings. We need to add a quote to each:
"[CustName] = '" & Me.txtCustNum & "'"
So either:
"[CustName] = '" & Me.txtCustNum & "'"
or
"[CustName] = """ & Me.txtCustNum & """"
will work.
Now, you might ask, why would you ever use the double quote if the apostrophe will work? Well, if your data can have an apostrophe in it, (like O'Brian) you have to use the double quotes.

ASCII Code

Another possibility is to use the Chr$(34) character (which is the ASCII code for a quote) like this:
"[CustName] = " & Chr$(34) & Me.txtCustNum & Chr$(34)
This gives us the opportunity to deal with delimited values of all kinds programmatically. I suppose I could write one of my own, but Ken Getz wrote the classic routine for that. Anything I'd write would simply be a copy, so I'll show his:
(excerpted from "Microsoft Access 2.0 How-To CD" (by Getz, Feddema, Gunderloy,and Haught and published by the Waite Group Press)
Function FixUp (ByVal varValue As Variant) As Variant

'Add the appropriate delimiters, depending on the data type.
'Put quotes around text, "#" around dates, and nothing
'around numeric values.

    Dim strQuote As String
    ' strQuote contains the ANSI representation of
    ' a quote character
    strQuote = Chr$(34)
    Select Case VarType(varValue)
        Case V_INTEGER, V_SINGLE, V_DOUBLE, V_LONG, V_CURRENCY
            FixUp = CStr(varValue)
        Case V_STRING
            FixUp = strQuote & varValue & strQuote
        Case V_DATE
            FixUp = "#" & varValue & "#"
        Case Else
            FixUp = Null
    End Select
End Function
To use this function, copy it into a general module, and then call it like this:
"[CustName] = " & FixUp(Me.txtCustNum)
Using this method, it doesn't matter what data type or delimiter, the function fixes it automatically.

Mixed Apostrophe and Quotes

One last problem. What if you have both embedded apostrophes and quote marks in your string value? For instance, you have a height value which is a string with feet and inches like this: 6' 3". This will cause a problem regardless of which delimiter you use.
However, there's a solution for that too. AD Tejpal has developed a comprehensive solution, which you can find here: http://www.rogersaccesslibrary.com/forum/topic113.html
In my next post, I'll look at some in-depth examples of how Domain Functions are used.
.