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.
.

Domain Functions Demystified

Introduction

In Microsoft Access, domain functions work like mini-SQL statements that can be used in queries, forms, reports, or code. Domain functions can also be used in places where SQL statements cannot, like the control source of a textbox. Like an aggregate (or totals) query, the domain functions build a resultset (that is, a set of records) and then apply a function to it. Unlike a query, however, a domain function can return ONLY one value.

Access has several built-in domain functions, the most common of which are: DCount(), DLookup(), DSum(), DAvg(), DMax(), DMin(), DFirst(), and DLast().

• DCount() returns the number of records of the resultset.
• DLookup() returns the value of a field in the resultset. If the resultset has multiple values, it returns the first one.
• DAvg() averages the values of the indicated field of the resultset.
• DMax() and DMin() find the highest and lowest values (respectively) of the resultset.
• DFirst() and DLast() finds the first and last values (respectively) of the resultset.

Note: At first glance, DFirst and DLast appear to be same as the DMax and DMin, but it they're really very different. DFirst returns the very first record in the recordset, which may not be the minimum value. Likewise, the last record in the recordset may not be the maximum. In general, I avoid DFirst and DLast as being less than useful.

One popular myth is that domain functions are slower than other methods. This is not true.  In some cases, the domain functions can be as fast or faster than other methods.  If performance is an issue, you should test both methods to and see which performs better.

Syntax

Domain functions have three arguments (the third is optional, however). The 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)

Each argument must be a string expression and must, therefore, be surrounded with quotes. This can be a problem when the Criteria Expression must also have quotes in it. I'll get to that a bit later.

Just like SQL Where clauses, some values must be delimited and others are not. For instance:

• Numeric values do not need delimiters (DLookup example below).
• Date values need the # delimiter around them (DMax example below).
• Strings need either the apostrophe (') or quote (") delimiters (DSum example below).

Examples of Simple Domain functions

• DCount("EmpID", "tblEmployee")Counts the number of records in the Employee table
• DLookup("SSN", "tblEmployee", "[EmpID] = 16")Returns the social security number of employee number 16.
• DMax("OrderDate", "tblOrders", "[OrderDate] < #1/1/2009#")Returns the latest order date from the Orders table which is before 1/1/2009
• DAvg("Cost", "tblProduct", "[Category] = 'printer'")
  Returns the average cost of all printers from the Products table.
• DSum("Cost", tblProduct", "[Category] = 'printer' AND [Manufacturer] = 'Epson'"or
  DSum("Cost", tblProduct", "[Category] = ""printer"" AND [Manufacturer] = ""Epson"""
  Returns the total cost of Epson printers from the Products table.

Because the entire Criteria Expression must be string delimited, the expression evaluator will get confused when using the quote to delimit a sting value, so you have to use two quote marks ("") in place of one ("). I'll discuss this in a later post.

Variables in Criteria Expressions

So far, I've just used hard-coded values in the Criteria Expression. That's not the most useful application of domain functions. Domain functions become most useful when you use a variable for the value in the Criteria Expression.

For instance,

DSum("Price", "tblOrderDetails", "[OrderNum] = 1")

will return the total price for a particular order (1). (The value is not delimited with quotes in this case because order number is a numeric field.) But if I put this expression in the control source of a textbox on a continuous form, it would always show the same value, regardless of the other values on the screen.

However, if I use a variable in place of the hard-coded order number, it becomes much more useful. Taking the OrderID from each record, gives me:

DSum("Price", "tblOrderDetails", "[OrderNum] = " & Me.txtOrderID)

In the first example, it is calculating the total for order 1 regardless of the other values in the row. In the second, it calculates the total based on the order number of that row.

Criteria expressions in domain functions are confusing, so it might be useful to take a closer look at them, which I will do in my next post: Domain Functions Demystified: Criteria Expressions.

.

Should I use the Compact On Close feature of Access?

I generally try to dissuade people from using the Compacting on Close feature of Microsoft Access, especially in a multi-user environment. Why?

Well, there are a variety of problems.

First of all, if your database bloats so quickly that it needs to be compacted every time it closes, then you have a design issue that should be addressed. See my blog post: How can I How Can I Compact my Access Database Less Often?

The two major causes of bloat are 1) importing data to a temporary table for processing and 2) creating temporary, intermediary tables with Make-table queries or other methods.

The data import problem can be addressed by linking a table or file for processing rather than importing it. If it can't be linked, it can be imported into a second, temporary database and then linked. This temporary database can be deleted later. I have a sample on my website called ImportToTempDatabase.mdb, which illustrates how to do this.

The problem of intermediate tables can also be solved by use of a temporary, local database which you create at startup and delete on database close. The solution is similar to the data import solution.

Secondly, compacting a database that doesn't need it will actually slow down the database some.  Not a lot, perhaps, but some.  Every database needs working space that Access has to create.  If your database grows a lot on first opening. but then very little on subsequent uses, this isn't bloat. It's the normal working space that your database needs.  Removing that working space every time you close the database requires Access to create it again the next time you use it.

Thirdly, it may be useless. A majority of Access databases are (or should be) in a Front-End/Back-End (FE/BE) configuration. This is often called a "split" database, where the tables reside in a separate database from the one which holds the queries, forms, reports, and code. Compacting on Close will only effect the database actually being used, which in most cases is going to be the FE. Unfortunately, it is the BE where the bloat actually occurs, so at best, compacting on close is useless, at worst, it's actively dangerous. Which leads me to the next point.

Fourth, and most importantly, there is a chance of database corruption any time you compact the database.  Admittedly, with more reliable networking hardware and software, this chance is smaller than in years past, but it's still there.   A small database on your local drive probably has little chance of corruption.  However, even a single-user database if it's very large and on a network has a chance of some network glitch causing the whole compact/repair to fail.  A failure during a compact means the database is toast.  There is no recovery.

A large, multi-user database on a network has a much greater chance of failure. 

So, I prefer to do a compact as a deliberate action rather than a hidden default.  Often, when compacting a large database on the network, I will copy it to my local drive, compact it locally, then upload it back to the network.  This gives me a backup in case anything goes wrong.

Also, if you compact the database programmatically (I have samples on my website: CompactDatabase.mdb and CompactBackEndDB.mdb), you have control to make a backup if you wish.

 

 

Office DEVCON in Australia

Today, I'm in Australia, speaking at the Office DEVCON 2010.  I'm very excited to be asked to present here and it's a wonderful opportunity to see Australia.

My topic is: Data Warehouse in Access. Ridiculous?  Not so! 

The premise is that there is nothing inherent in the definition of a data warehouse that excludes the use of Access as an implementation platform.  Certainly, Access has limitations of size and speed, and I'm not proposing its use as an Enterprise Data Warehouse.  What I am proposing, however, is you can use the principles of data warehousing (dimensional model, calculated columns, etc) to make a super fast reporting database for your transactional system, especially with respect to aggregate data reporting.

There are two sessions.  Part 1 discusses the structure of an Access data warehouse (or data mart if it makes you more comfortable).  Part 2 discusses various methods of reporting from your warehouse/mart.

For those interested, the session PowerPoint presentations and accompaning articles can be downloaded here:

Datawarehouse_in_Access_Part_1.zip (3 MB)
Datawarehouse_in_Access_Part_2.zip (3.7 MB)

Office Automation Samples:
ExportToExcelCharts.mdb
AutomatingWordFromAccess.mdb

.

New Sample: Form_TreeView3TierCascaded

by A.D. Tejpal
 
This sample db demonstrates three tier cascaded treeview control for student tests and grades, with two way synchronization visa-vis associated subforms.

Treeview at left displays classes and students while the second treeview shows different levels of tests for various subjects - for the class in question.

Third treeview displays test results for current student, showing date of current test and marks obtained. Grades can be entered / edited conveniently just by clicking the pertinent check boxes.

For convenient viewing, as soon as a given class node caption is clicked or navigated to (say by Up / Down arrow keys), it expands, displaying all student nodes belonging to that class. Simultaneously, all other class nodes get collapsed.

For navigation as well as editing, the treeview and subform are mutually synchronized. Any action on treeview is reflected on corresponding record in the subform and vice versa.

You can find the sample here:
http://www.rogersaccesslibrary.com/forum/Form-treeview3tiercascaded_topic548.html
 
.

New Sample: Form_DsContSetDisplayPos

Form_DsContSetDisplayPos

by A. D. Tejpal

 This sample db demonstrates positioning of selected block of rows in desired manner in the display window. Datasheet as well as continuous forms are covered, with the option to set start position of first row of selected block at top, middle or bottom of display screen.

You can find the sample here: http://www.rogersaccesslibrary.com/forum/Form-dscontsetdisplaypos_topic547.html

.

Normalizing City, State, and Zip

Recently, I ran into a question on the internet about normalization that I thought would be good to repeat.

THE QUESTION:

 I'm toying with the idea of starting a new project, so I'm in brainstorming mode for table design. I'll be recording customer information in this application. Typical stuff: First and Last Names, Company, Street, Apt, City State and Zip, Phone numbers(s) and extensions, E-mail.

How do you guys recommend setting up the tables for City State and Zip? I was thinking that I would have:

TBL_General_State
PKStateID
StateAbbr (Limited to 2 letters)
StateName

TBL_General_City
PKCityID
FKStateID (Lookup to TBL__State)
CityName

TBL_General_Zip
PKZipID
FKCityID (Lookup to TBL__City
ZipCode

My customer information then would record only the zip code (PKZipID). And I could then use queries for the state, city, and zip information for forms, reports, etc.

Or is this beyond overkill?

ANSWER:

 By strict normalization theory, having City, State, and Zip in the same table violates the 3rd Normal Form because there are functional dependencies between those fields. However, functional dependencies are not all the same. There are strong dependencies and weak dependencies.

A strong dependency is one in which the value of a dependent field MUST be changed if another field is changed. For instance, suppose I have Quantity, Price, and ExtendedPrice, where ExtendedPrice is a calculation of the other two. If I change either Quantity or Price, the ExtendedPrice MUST be changed.

A weak dependency is one in which the value of a dependent field MAY be changed if another field is changed. City, State, and Zip are examples of weak dependencies. If I change a person's city, I may not have to change their state. They may have moved within the same state. Likewise, if I change the state, I may not have to change the city. There is, after all, a Grand Rapids, Michigan and Grand Rapids, Minnesota. The relationship between city and zip is even more complicated.

Now, it is possible to represent these fields in a fully normalized fashion, but I contend that it is more trouble for very little gain. There are two main reasons for normalizing data: minimize redundant data and maximize data integrity. Both of these can be achieved by using lookup tables for City and State without trying to represent the relationship between the two. A zip code could be mis-typed, of course, but it could also be mis-selected from a list, so to my mind there's no real reason to have a lookup table.

If you did normalize these fields, you could have a selection process that would present all possible combinations of values if you selected the City. For instance, if you had a combo box for City, you could have cascading combo boxes to select only the appropriate States and Zip codes. But it would be just as easy to mis-select the right value from this list as it would be to mis-select from independent lookup tables. And, of course, you'd have to create and maintain these relationships.

Therefore, normalizing City, State, and Zip adds a complication to your data model for very little gain, and in my opinion, is a good example of when to denormalize.

.