THE ANNOYANCE: I’m writing code, and I need to refer to a subform control’s Visible property. Do I use dot or bang? Do I need brackets or not? I’m groping in the dark here!

THE FIX: The syntax VB uses to refer to database objects and their properties is a bit forbidding and certainly nonintuitive—but once you understand it, it’s not that big a deal. For analogy’s sake, consider the file- and pathnames on your computer. Every file has a short name (such as Readme.txt) as well as a full pathname (such as C:CartoonsDilbertReadme.txt). The path specifies exactly where to find that particular file, starting from anywhere on the computer.

Similarly, every database object and property has a short name (such as “txtOrderTotal” or “Visible”) and a fully qualified name (such as “Forms! frmOrders!txtOrderTotal” or “Reports!rptOrders.Visible”). In many situations, you can use the short name—for instance, when you’re distinguishing one control from another control on the same form. In other situations, such as when you’re referring to a control on a form from within a query, you must use the fully qualified name, or Access won’t be able to find it.

In the Windows filesystem, path locations are always separated by slashes (). Access uses two different separators: the bang (!) and the dot (.). Bang is used to refer to user-created objects such as forms, reports, and controls. Dot is used to refer to Access’s built-in objects and properties. Consider this example: Reports!rptOrders.Visible. The first element, Reports, is the name of a built-in collection that contains all the open report objects. The user-defined report, rptOrder, is distinguished with the bang notation. The built-in Visible property of the orders report is identified using the dot notation.

In many cases, you’ll notice that Access adds brackets around object names (and sometimes property names), like this: [Reports]![rptOrders].[Visible]. These brackets are not normally required, but they do no harm. However, there are two situations in which brackets are required. First, if an object name contains spaces or other nonstandard characters, Access needs the brackets in order to handle the name properly. Second, if the name occurs in a context (such as query criteria) where Access might mistake the object name for a text string, the brackets tell Access to treat it as the name of an object. (See “[Brackets] Versus “Quotes”," later in this chapter.)

Finally, we should add that there are two other ways to reference user-defined objects: using quoted names and indexes. A quoted name looks like Reports("rptOrders") and means the same as Reports!rptOrders. Quoted-name syntax permits storing an object name in a variable and then referencing it, like this:

	Dim strReportName As String
	strReportName = "rptOrders"
	Reports(strReportName).Visible = False

Quoted-name syntax is handy, but just in case this seems too straightforward, know that Access doesn’t accept it in queries. Figures!

Index notation, which refers to members of a collection by a numeric index, is most useful in situations where you need to iterate over all the objects in a collection. For example, Reports(0) refers to the first report in the Reports collection, and Reports(Reports.Count–1) refers to the last. Just remember that collections use a zero-based numbering system.

THE ANNOYANCE: My payroll form has a simple expression, =(salary + bonus), that totals salaries and bonuses. I know this expression should never be empty, because salary is a required field—but it’s coming up blank.

THE FIX: The most common reason an expression comes up blank is that it’s null. Even though part of your expression (the salary field) is not null, what matters is whether the entire expression evaluates to null—and in this case it does, because when you add null to a number you get null. Note that null is not the same as zero!

The fix is to use the Nz function, which converts null values to a default value that you specify. In your case, instead of =(salary + bonus), use =(salary + Nz(bonus,0)). When the bonus field is null, the result is (salary + 0) (because we specified a default of zero). When the bonus field has a value, you get (salary + bonus), as you should.

Nz is not just useful with numeric values. It can also be used with a text field, as in Nz(firstName, ""), to return an empty string if the field is null. You can also have Nz return something other than zero or the empty string—for instance, if you use the expression ="Marital status is: " & Nz(maritalStatus, "unknown"), when marital status is null, Nz will return “unknown.”

A more obscure reason for unexpected blank values is zero-length strings (i.e., the empty string, “”). Here’s how the problem can arise. Suppose you have a query in which you set criteria on the lastName field that dictates it can’t be null. You open up the query and see blanks in the lastName field. How is this possible? Well, it’s possible if your field is set to allow zero-length strings (check the Allow Zero Length property in table Design View). Zero-length strings are not null, they’re just blank. The bad news is that, by default, Access allows them; the good news is that they’re not that easy to enter. You can find records containing zero-length strings by setting the Criteria line in the query design grid of the field in your query to “”.

THE ANNOYANCE: I’m trying to apply conditional formatting to the balances on our payments form, using this expression: [balance] <= 0. But the formatting is applied to the wrong records!

THE FIX: This annoyance has nothing to do with conditional formatting—it’s about nulls. When expressions give unexpected results, one of the first things to check for is null values. (This isn’t a problem specific to Access; nulls are a confusing part of life in the database world at large.) If any part of an expression can be null, you must take that into account. A null value in a field means that no data was entered. Sometimes this means the value is unknown (e.g., DateOfBirth) but it can also mean the data doesn’t exist (e.g., SpouseFirstName). You can sometimes avoid nulls by making a field required and defining a default value (such as “None”) for cases where no data is entered, but often this is a poor solution. For example, you can’t enter “Unknown” in a Date/Time field, and a SpouseFirstName of “None” might mean either no spouse or no first name supplied.

Null values are also confusing because they don’t behave like ordinary values. Since they represent unknown values, they are treated as values that haven’t yet been defined. For example, suppose you’re looking for records where [amountPaid] = [rebate]. Records with null values in either field will be excluded from the result, because a null value is not equal to anything—not even null. Null does not equal null. This is pretty unintuitive, but it does sort of makes sense: just because I don’t know Mozart’s birthday or Beethoven’s birthday doesn’t mean they have the same birthday. Similarly, suppose you’re looking for all last names other than Smith, using an expression such as this: [lastName] <> "Smith". Null names will not show up in your results, because null values are not not equal to anything, either.

In short, nulls cannot be compared—to anything. They’re not less than anything, they’re not greater than anything, they’re not equal to, before, or after anything. (And that explains why [balance] <= 0, above, is giving unexpected results—a null balance is not equal to zero.)

To help you cope with nulls, Access provides a number of special functions and expressions:

Another instance when nulls cause problems is when you feed them to certain VB functions. For instance, suppose you are using CDbl() to turn integers into double-precision floating-point numbers for the purposes of a calculation. If you feed CDbl() a null value, you’ll get a runtime error, “Invalid use of Null.” You need to either use Nz() to convert the nulls to some other value—CDbl(Nz([length],0))—or ensure that there are no nulls in the records that get fed into the calculation.

The Group By clause in queries is the one exception to the rule that null does not equal null. You may notice that if you group on a field with null values, all the null records are put into the same group. Although this behavior is inconsistent, it’s useful when you need to group on a field that contains null values. If you want to exclude these records, simply put Is Not Null in the Criteria line of the grouping field.

Finally, there’s one additional twist to working with nulls if you are writing code with ADO (see “DAO Versus ADO” in Chapter 0). When you supply criteria to ADO’s Find function, you must test for nulls using the Null keyword, rather than any of the expressions discussed above—for example, [balance] = Null or [balance] <> Null. This is a common practice in VB code, but it’s different from the usual tests used in Access SQL.

THE ANNOYANCE: I’m using a complex expression on a form, and it keeps coming up #Name?. I don’t know where to begin fixing it.

THE FIX: Trying to guess what’s wrong with an expression can be maddening. Start by working through the list of common causes for #Name? and #Error? messages in “#Name? and #Error?” in Chapter 1. There’s no way we could list every possibility, but you may get lucky. If not, evaluate the whole expression in the Immediate window (see the upcoming “Rule 3: Examine your data”)—this will often produce a more illuminating error message. If that doesn’t work, take the following systematic approach to debugging your expression. As an example, we’ll use this expression, which produces a #Name? error:

	FormatCurrency(Nz(DLookup(orderTotal, tblOrders, "orderId = """ & cboOrder)))

The following three debugging tips should help you track down the source of the problem.

	DLookup(orderTotal, tblOrders, "orderId = """ & cboOrder)

	varX = DLookup("[CompanyName]", "Shippers", "[ShipperID] = 1")

	DLookup("[orderTotal]", "tblOrders", "orderId = """ & cboOrder)

Rule 3: Examine Your Data

What about the data we’re feeding into this function? What values do the arguments have at the moment Access tries to evaluate the function? Garbage in, garbage out is the programmer’s motto: even if your syntax is correct, if you’re feeding in bad data you’ll get bad results. In this case, we check that orderTotal, orderId, and tblOrders are all correctly spelled names in our database—which they are. We also check the value that cboOrder has when the expression shows #Error?.

To check the value of a live control like cboOrder, we’ll use the VB Immediate window. Ctrl-G opens the VB Editor with the Immediate window at the bottom. You can enter an expression in the Immediate window and have Visual Basic process it immediately, revealing what happens behind the scenes when the same expression is used on your form. Type a ? (which is a shortcut for the Print method) before your expression and press Enter at the end of the line to display the result in the window (see Figure 7-1).

Figure 7-1. The Immediate window in the VB Editor lets you run procedures and evaluate live expressions. In this example, the current value of the cboOrder combo box is 2.

Note that we used the fully qualified name of the combo box; otherwise, the Immediate window won’t know where to find it. When you type this expression into the window and press Enter, the result (in our case, 2) appears directly below it. (Visual Basic is reading this value from the combo box on the form; obviously, the form must be open for this to work.) As expected, the combo has the value of a valid order ID.

What the heck else could be wrong? Let’s check the value of our whole criterion string:

	"orderId = """ & cboOrder

To do this in the Immediate window, you must qualify the combo box again, like this:

	?"orderId = """ & Forms!frmCustomers!cboOrder

The result is:

	orderId="2

Now that’s interesting! There’s a quotation mark in the middle of the result, which can’t be right. We either need two quotes or no quotes—and since this is numeric data, it’s the latter. (See “"Quotes”, #Quotes#, and More #%&@!! “"""Quotes"""”," later in this chapter.) Once we’ve cleaned up those extra quotation marks, our DLookup expression looks like this:

	DLookup("[orderTotal]", "tblOrders", "orderId = " & cboOrder)

Try this out, and you’ll see that the error is gone. Now, put this back into the original expression, and test again at each stage until the whole thing is reassembled and running like a watch.

THE ANNOYANCE: I have a simple calculated field on my report, [totalAmount] + 10, and I’m getting a syntax error message. What could possibly be wrong with this syntax?

THE FIX: You forgot the equals sign! If you omit the equals sign from the beginning of an expression, you’ll get an unhelpful error message such as “Syntax error in query expression <expression>” (see Figure 7-2), “<databasename> can’t find the field <fieldname> referred to in your expression,” or even the classic #Name? or #Error? message. In your case, the fix is simple—just put the equals sign where it belongs, like this: =[totalAmount] + 10. The one place you don’t need an equals sign is in the Criteria line of the query design grid.

Figure 7-2. This is one of several misleading errors you can get if you omit the equals sign at the start of your expression.

THE ANNOYANCE: I’m trying to use DLookup() to find a record matching a specific date, and I can’t seem to get the quotes right. I’m not getting an error, but I’m not finding the record, either.

THE FIX: Quoting data in Access can be a little tricky. There are four different data delimiters—five, if you count no delimiter as a kind of delimiter (Figure 7-3 shows some examples). That’s just too many—and it’s only the beginning of the problem. When you have to embed quoted data inside other quotes, it’s really easy to make errors. In this fix, we’ll clear up some of the confusion about using quotes.

Figure 7-3. A query showing the use of three different delimiters in its Criteria line. The first column is a date, the second is a field, and the third is a text string.

First, the delimiters:

Now, here’s where things get more complicated. There are many situations where you need to construct a text value that itself contains delimiters. For example, as described in the introduction to this annoyance, this problem often arises when you need to create the criteria expression for DLookup(). A typical DLookup criteria looks like this:

	DLookup("orderTotal", "tblOrders", "orderId = " & [orderId])

Here we’ve concatenated a number (taken from the orderId field) to a text string ("orderId = "), resulting in output such as orderId = 5. That works great with numbers, but it’ll produce an error with other data types, such as dates and text. For dates, you need something like orderDate = #10/03/2003#; for text, you need something like partName = "Widget-7“.

Here’s how to handle dates:

	DLookup("orderTotal", "tblOrders", "orderDate = #" & [orderDate] & "#")

This DLookup finds the order total that corresponds to the specific date found in the orderDate field. All we did was treat the pound sign as a text character, concatenating it as part of the other strings. This lets us embed the date delimiters into the text string.

It would be nice if you could do the same thing for quotes around text data, but you can’t. You can’t embed a quotation mark inside quotes, because Access won’t know which quote actually delimits the string. Instead, you must use a pair of quotes wherever you need an embedded quotation mark. For example, Access translates the text "Embedded "" Quote" into Embedded " Quote.

Here’s how you’d construct DLookup criteria for a text field:

	DLookup("orderTotal", "tblOrders", "partName = """ & [partName] & """")

THE ANNOYANCE: I’m trying to find documentation on the Like operator, but when I ask the Answer Wizard to search for “Like,” Access tells me to rephrase my question.

THE FIX: Most operators and functions come from Visual Basic and are documented in VB Help, but three (Like, In, and Between) come from SQL. In addition, because they’re named after very common words, the Help search engine considers them too common to even bother searching for. (Actually, it will search for Between—but it won’t find the page you’re looking for; Access 2003 will find entries for Like, but that’s it.)

There is documentation for these operators, though. In Access Help’s Contents tab, look in Microsoft Jet SQL Reference → Overview → SQL Expressions (see Figure 7-4). Also see “Simple Validation Rules” in Chapter 3 for examples of how these operators are used. You can find additional help for the Like operator by searching for "Like operator” in VB Help.

Figure 7-4. If you’re willing to dig around in the table of contents, you can find help on these SQL operators.

THE ANNOYANCE: I’m trying to create a validation rule using the Like operator, and Access keeps changing it to Alike. Also, it tells me that my data violates the rule, but it doesn’t!

THE FIX: If Access changes Like to Alike, that means that your database is set to use SQL-92 syntax. In this case, your wildcards (* and ?) should be % and _ instead. That would explain why your validation rule isn’t working as expected. To change back to Access SQL syntax, close all database objects and then choose Tools → Options, click the Tables/Queries tab, and uncheck the “This database” box under “SQL Server Compatible Syntax (ANSI 92).” (This problem occurs only in Access 2002 and later versions.)

THE ANNOYANCE: I’m using the Expression Builder to construct an expression that will run on our customers form and refer to a control on the orders subform. I found the subform control, but the Expression Builder doesn’t list the field I need. How do I make this thing work?

THE FIX: The Expression Builder appears when you click the Build (…) button that you’ll see in certain text boxes in a properties sheet. It can be pretty confusing, especially for those who are new to expressions. For one thing, it lets you construct all sorts of invalid expressions; worse, it provides scant guidance on the syntax of functions in general. So is it good for anything? Maybe—as a browser of the objects and functions in your database.

Since expressions are used to manipulate values based on other data, you’ll often need to refer to controls, queries, and the like as you create them. For example, if you need to refer to a control on your subform, the Expression Builder can save you from opening the subform and looking up the exact name of that control. Of course, that assumes you can find the control you’re looking for.

Here’s how the builder works. In the bottom-left panel, select an item (see Figure 7-5). Its contents will appear in the middle panel. Click an item in the middle panel, and that item’s properties (or contents) will appear in the right panel. Double-click anything in the middle or right panels, and it will show up in the top panel as part of an expression.

If you can’t find the control you’re looking for, it’s probably because you didn’t start with that object’s parent in the left pane. In your case, you need to double-click the folder icon next to your form name. When the folder opens, you’ll see that it contains your orders subform. Click on the subform folder, and its contents will appear in the middle panel. Now you’ll be able to find the controls on your subform. Locate the control that you want in the middle panel and double-click it. Access will put something like this into the top panel: [sctlOrders]. Form![txtSumOrderTotal]. That’s nice—except Access forgets to place an equals sign at the beginning of the expression. You’ll have to add it in.

Figure 7-5. Double-click frmOrdersByCust in the left panel, then click sfrOrders to access its contents in the middle panel. Double-click txtSumOrderTotal to insert it into the expression. Note that Access does not supply the required equals sign at the front of the expression.

If you want help building Visual Basic functions, you won’t get much in the Expression Builder. For tips on using VB Help, see “Getting Help” in “Tame the Visual Basic Editor,” later in this chapter.

THE ANNOYANCE: I looked up the Chr() function because I need to insert line-feed characters in a text string, and I found that VB Help also lists a function called Chr$(). But when I click on Chr$(), I just get the same help page I got for Chr(). Is there a difference?

THE FIX: Help is just so unhelpful sometimes. Beats me why they couldn’t put in a link to the (badly named) topic “Returning Strings from Functions”—that’s where you get the lowdown on the distinction between Chr$() and Chr(). In VB Help, go to the Contents tab, open the “Visual Basic Conceptual Topics” item, then open “Returning Strings from Functions.” (If you can’t find that, see “Getting Help” in “Tame the Visual Basic Editor,” later in this chapter.)

There are about 30 functions that have “dollar sign” versions. As a general rule, you’ll be fine sticking with the plain versions—the main difference is that the plain functions return a Variant data type and the dollar sign functions return a String data type. It’s easy to remember which is which if you think of the “$” as the “S” in String.

The String data type handles text, and nothing but text; Variant can include text, numbers, dates, and null values. Because of this, plain functions typically handle nulls better than their dollar sign counterparts. For example, suppose you have an expression like Trim(LastName). If LastName is null, Trim() returns null, which is fine. Trim$(), however, can’t handle a null value; instead, you get a runtime error—“Invalid use of Null”—that brings everything to a grinding halt. Be aware, though, that not all plain functions that have dollar sign counterparts can handle nulls. For example, Chr() will also throw a runtime error if you feed it a null.

Why would you ever use a dollar sign function? If you’re concerned about memory use, the dollar sign functions are slightly more RAM-efficient. Also, there are some situations—such as when you’re writing directly to files—where your output must be typed as String, not Variant.

THE ANNOYANCE: On my orders form, I set the default value of the amount received equal to the order total field, and Access added brackets around it, like this: =[orderTotal]. Then I set the validation rule to 15 percent of the order total, and Access added quotes: > .15 * "orderTotal". Why did Access set orderTotal first with brackets, then with quotes? And why does every value I enter violate the validation rule?

THE FIX: Access adds brackets so you can use spaces in field names and object names, such as [Employee Last Name]. (To see why this is a bad idea, read “Bad Field Names” in Chapter 3.) If your names don’t have spaces, you probably won’t need to use brackets. However (surprise!), Access is not entirely consistent about this, and in certain places brackets are required if you want to refer to a field or object, such as a control. In the Default Value property, for example, brackets are required, and Access adds them for you if you leave them out. In the Validation Rule property, brackets are also required; however, if you omit them, Access adds quotes instead (see Figure 7-6). This schizophrenic behavior means that Access treats your field name as if it is a simple text string. The validation rule > .15 * "orderTotal" is meaningless, since it attempts to compare a numeric field to the string "orderTotal". It will always fail. Replace the quotes with brackets, as in > .15 * [orderTotal], to make it behave.

Figure 7-6. This validation rule, which takes 15 percent of a text string, is not what you intended. Use brackets to ensure that Access knows you’re referring to a field, not a string.

Another place where Access’s penchant for quotes commonly causes trouble is in the Criteria line in the query design grid. As in the examples above, if you want to refer to a field or control name, make sure it’s surrounded by brackets, even if you have to put them there yourself.

THE ANNOYANCE: In Outlook, I can set an option to display person names as “First Last” or “Last, First”—but in Access my mailing list names come out all mushed together, with extra spaces thrown in, too.

THE FIX: It’s standard practice in database design to use separate fields for first and last names—but this means that you must recombine them whenever you need to display the full name in a query, form, or report. Fortunately, it’s not too hard to do this, but there are a few tricks that make it work better. In a query, for instance, suppose you want a “First Last” display. On the Field line, you could add an expression such as this: FullName: FirstName & " " & LastName. The ampersand concatenates strings, so this expression just sticks a space in between the first and last names. That works fine as long as both fields have values, but if any first names are missing (i.e., null), it will tack on a space at the front of the last name, so it won’t line up right. A better version is FullName: (FirstName + " ") & LastName. This trick relies on the fact that the plus sign is also a concatenation operator for strings. The difference between + and & is that if you “plus” a null value, the entire result is null, whereas if you “ampersand” a null value, it gets ignored. The result is that spaces are added only when first names are not null. (You could do the same thing with the IIf (Immediate If) function, but the “plus” gambit is more concise.) Table 7-1 lists some common name expressions with different name orders, for databases with and without spouse names.

THE ANNOYANCE: I want to set up an orders query that shows last month’s orders. I’ve looked at the Date functions, and there doesn’t seem to be a way to do it.

THE FIX: Although dates show up everywhere in databases, it can be fiendishly hard to craft an expression that delivers just the result you want. The key is understanding how Access represents dates internally. (This approach is complemented by using a calendar table; see “Working with Calendar Dates” in Chapter 4.)

Every date in Access is really a date and a time of day. When you input a date such as 10/21/2004, what Access stores is 10/21/2004 00:00:00—which gives both the date and the time in hours:minutes:seconds format, using a 24-hour clock (00:00 is the same as 12:00 AM). You don’t normally see the time because, by default, Access uses the General Date display format, which omits the time component if it’s zero. Despite what’s displayed, dates and times are stored internally as just one data type: Date/Time.

Date/Times are represented internally in Access as numbers of type Double (they’re double-precision floating points, but the name is Double). The integer part of a Date/Time represents the number of days since the base date (12/30/1899), with negative numbers representing days before that date; the decimal part of a Date/Time number gives the time value as a fraction of 24 hours starting at midnight (see Figure 7-7). For example, 12:00 PM on 12/30/1899 is represented as 0.5, and noon on January 1, 1900 is 2.5. Because of this representation, if you want to combine a pure date value (where the time part equals 0) and a pure time value (where the date part equals 0), you can simply add them—and if you subtract two dates that have the same time, you’ll get the number of days in between.

Figure 7-7. Date/Time values are represented in Access as numbers, with the integer part representing the number of days since the base date, and the decimal part representing the time of day.

You can experiment with date numbers by typing ?CDbl(#some date #) in the Immediate window. You can compare dates using the usual comparison operators (<, >, <=, and so on), and you can specify a date range (endpoints included) using the Between…And operator. If you need to specify a literal date/time value, use the # sign, like this: #10/21/2004 12:35 AM #. The # sign does for dates what quotation marks do for text. (See MSKB 210276 for additional useful info about working with dates.)

Before we get into specific date problems, take a look at this list of date functions (see also “Visual Basic Functions” in the Appendix):

Now, let’s look at the solutions to some common date problems.

Find Overlapping Date Ranges

It’s common in the database world to track things like projects and events, which have both start and end dates. Often you need to calculate when different events overlap, but figuring out the exact test to use is a bear. Here’s how to do it: two events overlap when EventA.startDate < EventB.endDate AND EventA.endDate > EventB.startDate. Remember, with dates, < means “before” and > means “after,” so this is saying that event A overlaps event B if event A starts before event B ends, and event A ends after event B starts! If these conditions don’t give you a headache, they may make a certain amount of intuitive sense. To convince yourself that they are both necessary and sufficient, take a look at Figure 7-8.

Figure 7-8. When the dates of two events (A and B) overlap, there are four possible relationships between their start and end dates. You can see graphically that in all four cases, Event A’s start date comes before Event B’s end date, and Event A’s end date comes after Event B’s start date.

THE ANNOYANCE: Our projects table specifies a start and end date for every project. But the dates used for some projects include times as well, and this makes it hard to do date comparisons and to work with date ranges. What’s the solution?

THE FIX: First, consider how Access looks at dates. For example, if you set query criteria such as Between #12/01/2003# And #12/15/2003#, you won’t include dates such as #12/15/2003 11:00 AM#. That’s because #12/15/2003# really means #12/15/2003 00:00:00#, which comes before #12/15/2003 11:00 AM#. (See the previous Annoyance for more on the way Date/Times work.)

There are a few ways around this problem. You can add time specifications in the Between…And operator, like this: Between #12/01/2003# And #12/15/2003 23:59:59#. In this example, we added the latest possible time to 12/15/2003, to include the whole day. But sometimes it’s simpler to use comparison operators, bearing in mind time values as well as dates. For example, this is equivalent to the previous expression: >= #12/01/2003# And < #12/16/2003#.

It would be nice if Access provided functions to give you the pure date or pure time parts of a Date/Time value. It doesn’t, but you can create these yourself, like this:

For myDateTimeValue, you can substitute any valid Date/Time field or constant (such as #12/15/2003 3:43AM#).

THE ANNOYANCE: I need to display the number of working days between the start dates and end dates of every job. I tried using DateDiff with the "w" option, but it doesn’t work.

THE FIX: It’s idiotic, but DateDiff's "w" option—which, by the way, stands for “weekday”—computes the number of weeks between two dates, not the number of weekdays (AKA working days). Another option, "ww", computes the number of calendar weeks, giving you two different ways to count weeks, but no way to find out the number of working days.

There are two different approaches to this problem. First, you can use a custom VB function that counts the weekdays between two dates. This is simple and easy, but it won’t take holidays into account. If you need to skip holidays, you’ll either need to customize the function to skip them on an ad hoc basis, or use a calendar table (see "Working with Calendar Dates” in Chapter 4). Here’s a function for the first approach that accepts two dates and returns the number of weekdays between them. It just loops through every day between the two dates, incrementing a counter if the day is not a Sunday or Saturday:

	Public Function weekdays(dateStart As Date, dateEnd As Date)
	    Dim intNumDays As Integer
	    Dim dateNext As Date

	    intNumDays = 0
	    dateNext = dateStart
	    While dateNext <= dateEnd
	            If Not (Weekday(dateNext) = vbSunday Or _
	                             Weekday(dateNext) = vbSaturday) Then
	                    intNumDays = intNumDays + 1
	            End If
	            dateNext = DateAdd("d", 1, dateNext)
	    Wend
	    weekdays = intNumDays
	End Function

To use the calendar table approach, join it to your source table on the date field. Then set criteria on the calendar table fields. For example, you can add the isWorkDay field to your query and set its Criteria line to “True.” To exclude holidays, add the isHoliday field and set its Criteria line to “False.” To count working days, simply make it a totals query and use a Count expression.

THE ANNOYANCE: In order to handle an empty subform, I need to refer to its RecordCount property, but I keep getting a #Name? error.

THE FIX: The syntax for referring to subform and subreport properties is convoluted, but you’ll get used to it. This fix discusses subforms, but the same approach applies to subreports.

Here’s the short way you’d refer to a subform’s Recordset and RecordCount properties from the main form:

	sctlOrders.Form.Recordset.RecordCount

It takes two qualifiers to get to the subform:

Likewise, to get to a subreport, you’d reference the subreport control and its Report property.

So what’s the reason for this complexity? Access won’t let you add one form directly to another form. The only thing you can add to a form is a control. Therefore, Access has a type of control (the subform control) whose purpose is to hold a reference to a subform. Of course, every control has lots of properties (Name, Parent, and so on); the subform control’s Form property is where the reference to the subform is actually stored. Note that you never use the name of the subform itself, since the Form property points directly to it.

You’ll have to use a longer, fully qualified version of this name in VB code, or when referring to it from database objects that aren’t in the main form. For example:

	Me!sctlOrders.Form.Recordset.RecordCount
	Forms!frmCustomers!sctlOrders.Form.Recordset.RecordCount

See “.Dot, !Bang, and [Bracket],” earlier in this chapter, for more on fully qualified names.

You may have noticed that you don’t need to use the Form property when referring to controls that are on the subform. For example, you can reference a text box on a subform like this: sctlOrders!txtSumOrders. That’s because when Access sees a control name, it assumes you mean the subform. Of course, you still need to reference these controls via the subform control (i.e., sctlOrders)—not the subform (say, sfrOrders) that it contains.

To put it all together, here’s how you might set up a conditional expression, using VB’s Immediate If (IIf) function, to avoid referencing a subform control if the subform is empty:

	=IIf([sctlOrders].[Form].Recordset.RecordCount = 0, 0, [sctlOrders]! [txtSumOrderTotal])

THE ANNOYANCE: I’m trying to create a macro that runs a query, but there’s no macro recorder like there is in Word. Where do I start?

THE FIX: If you’ve only used macros in Word, you probably think a macro is a recorded set of keystrokes. Macros in Access are quite different—they’re a way for non-programmers to write elementary Visual Basic code. Access provides about 50 predefined “actions” that you use to build macros—kind of like building a house using prefab walls.

The actions range from opening a form to importing a spreadsheet, and you combine them simply by listing them sequentially in your macro. To create a new macro, use Insert → Macro. The only required column is Action. Choose the action you want from the drop-down menu (see Figure 7-9), and then fill in the appropriate arguments in the lower-left pane. For example, the Beep action has no arguments; it just beeps. However, the OpenForm action needs to know which form to open, and it accepts optional arguments specifying the form view, Where condition, and so on.

Figure 7-9. There are over 50 choices on the macro Action menu—and no good documentation in recent versions of Access Help. Unbelievable!

Access Help used to provide a useful overview of macro actions, but at some point it got dropped—people must have complained that Microsoft was making it too easy. We’ve provided an annotated listing of macro actions, grouped by category, in the Appendix.

THE ANNOYANCE: I’m looking for a list of the VB Editor’s keyboard shortcuts, and I can’t find anything in VB Help. They don’t even list the VB Editor in Help!

THE FIX: Just when you thought Help couldn’t get any worse…if you’re like me, you press F1 to open Help, and it’s here where Microsoft’s designers have thrown you yet another curve ball. Pressing F1 from the Editor only opens up the Visual Basic Language Reference—at least, that’s all you’ll see on the Contents tab. But if you select Help → Microsoft Visual Basic Help, you’ll see topics such as "Visual Basic User Interface Help,” “How-to Topics,” and so on. Looking for those keyboard shortcuts? Open the “Visual Basic User Interface Help” item, then “Shortcuts.”

But there’s more than shortcuts to the VB Editor. While we have your attention (don’t turn that page!), let’s take a whirlwind tour of the VBE (see Figure 7-10), which has some elegant features and some ridiculous quirks.

Figure 7-10. The Visual Basic Editor, with some of its windows open. On the left is the Project Explorer—a file tree of all your code modules. In the middle is the code window, and above it, the Object and Procedure menus. On the bottom is the Immediate window.

Getting Help

In contrast to Access’s Help system, VB Help is actually quite well integrated into the Editor, and it can save you a great deal of time. When you’re typing code, you rarely have to look up the exact name of a property or the order of function arguments, since Visual Basic IntelliSense pops open little menus and parameter lists as you type. And you rarely have to type the complete name, since IntelliSense will complete it for you as you type—just press Enter, or a comma or a space, and IntelliSense completes the word (see Figure 7-11).

Figure 7-11. VB’s IntelliSense works as you type, supplying parameter lists for procedures and drop-down menus for named constants. It’s not a substitute for reading the docs, but it does save you the trouble of having to check them every time you want to use a procedure.

If you do need to look up the documentation for a method, object, or property in your code, simply place your cursor on that term and press F1. If there’s help available (and there usually is), VB Help will open to the correct page. Of course, this won’t work for your own custom procedures, but the Editor does make it easy to jump to procedure definitions from anywhere in your code. Simply place your cursor on the procedure name and press Shift- F2, and the Editor will open the module where the procedure is defined and scroll to the code. To jump back to where you were, hit Ctrl-Shift-F2. To browse all available objects (both custom and built-in) and their methods and properties, use the Object Browser (View → Object Browser). Again, context-sensitive Help via the F1 button is very useful here.

Finally, you’ll notice that the Editor checks syntax as you type, looking out for errors such as leaving off a matching parenthesis, omitting an equals sign, and so on. This is great—except when a warning message interrupts you when you’ve written half a line of code and you then need to go copy something in order to complete it. You can turn off these messages while in the VBE by choosing Tools → Options, clicking the Editor tab, and unchecking the “Auto Syntax Check” box. Your syntax errors will still be highlighted in red.

Debugging

The VB Editor provides the usual complement of debugging tools. Of course, everything that applies to debugging expressions (see “Debugging Expressions,” earlier in this chapter) applies to debugging code as well. The Immediate window is particularly useful when you want to check the current value of some property or expression.

For serious debugging, the workhorse of programmers everywhere is the lowly print statement. In Visual Basic it’s Debug.Print, which prints to the Immediate window. For example, to see what’s going on in a loop, you could add a print statement such as the following to your code:

	Debug.Print intCounter; strName

The semicolon is used to separate expressions in the same print statement.

You can set breakpoints (see Figure 7-12) by clicking on any code line in the left margin. When you run the code (say, by clicking a button on a form), it will halt at the breakpoint, and the VB Editor will open to that line of code. You can then use the Locals window to view the values of the variables defined in that procedure, as well as the values of relevant objects. To set a conditional breakpoint (for instance, breaking only when a certain expression is true), use the “Add Watch” command available from the right-click menu in the code window.

Figure 7-12. A breakpoint was set on the DoCmd.OpenForm line. When the code runs, it stops at the breakpoint and the VB Editor opens to that line. The Locals window shows local variables—in this case, the value of the NewData and Response arguments to the Not in List procedure.

THE ANNOYANCE: I’m trying to find a function that will convert month names into numbers between 1 and 12. VB Help just has a long, alphabetical list of functions.

THE FIX: Obviously, an alphabetical list is only helpful if you already know the name of the function. You’d think Microsoft would provide an overview of functions by topic area—and they do, sort of. The first problem is that you won’t find it in the Language Reference under Functions; that would make too much sense. Instead, you must look under Indexes/Lists, which breaks out categories such as math, dates and times, and so on. The second problem is that this list is incomplete, not especially informative, and strangely organized. (For a better overview of functions, we humbly suggest the Appendix in this book.) VB Help also has a Groups section that, despite the meaningless name, has some useful overviews of operators, operator precedence, and data types. It’s found in the Visual Basic Language Reference.

That said, unfortunately there is no built-in function that maps month names to integers. However, you could easily use a Switch function for this purpose (Switch(monthName = "January", 1, monthName = "February", 2,), and so on).

THE ANNOYANCE: I want to track which records get viewed, but I can’t figure out which form event to use. Forms have so many events—Current, Load, Open, Activate, and so on. How do I choose the right one?

THE FIX: There used to be two Help topics that gave a reasonable overview of events and event orderings: “Events and Event Properties Reference” and “Find out when events occur.” In its inscrutable wisdom, Microsoft seems to have taken the first topic out of circulation (although you can still find it on MSDN, and in Access 2000 Help). You can still get info about the order of events by searching for “events” in the Answer Wizard in Access Help, and you’ll find an annotated list of events, organized by category, in the Appendix to this book.

To keep track of which records are getting viewed, you’d probably use the Current event, which occurs every time the focus moves to a new record—Load and Open occur only when the form is first opened.

THE ANNOYANCE: I used the Combo Box Wizard to create a combo box that finds records on our employees form. But we get a syntax error for any employee whose name contains an apostrophe.

THE FIX: When names like O’Brien, O’Malley, O’Reilly, and Children’s Hospital cause an error such as “Syntax error (missing operator) in query expression 'lastName = "O'Brien",” it’s a sure bet that you’ve used apostrophes in your string criteria. It’s an easy mistake for a beginning programmer to make, but you’d think the Combo Box Wizard would know better!

Here’s what’s going on. There are many places in code (when running queries, opening forms, applying filters, and so on) where you’ll need to construct a criteria string like [lastName] = "O'Brien". Note that the target value of the criteria (here, O’Brien) must be enclosed in quotes. The problem is that these quotes must be embedded in the quoted string criteria, which is tricky to do (see “"Quotes”, #Quotes#, and More #%&@!! “"""Quotes"""”," earlier in this chapter). The correct solution usually looks something like this:

	Dim strCriteria As String
	strCriteria = "[lastName] = """ & Me!cboLastName & """"

But since all those quotation marks are confusing to look at, you may be tempted to use single quotes embedded directly in a quoted string:

	strCriteria = "[lastName] = '" & Me!cboLastName & "'"

This is cleaner, and it works because you’re allowed to quote the target value in single quotes instead of double quotes (for example, [lastName] = 'Murphy'). However, this trick fails if your target value itself contains a single quote—i.e., an apostrophe—because Access’s parser can’t tell where your string ends.

The moral of the story: don’t use single quotes when you construct string criteria. As for the Combo Box Wizard, you won’t run into this problem unless your primary key field is a text field that’s allowed to contain apostrophes. If that’s the case, you’ll have to open up the code (in the combo box’s After Update event) and replace each single quote with a pair of double quotes.

THE ANNOYANCE: I set up a macro in Access 2002 that deletes old data and imports new data from a text file. But every time it runs, I get a warning message about deleting data (see Figure 7-13). Isn’t there some way I can turn that off?

Figure 7-13. By default, Access warns you every time you run an action query.

THE FIX: Those warning messages are great when you need them—and very annoying when you don’t. If you really know what you’re doing, you can disable them entirely by choosing Tools → Options, clicking the Edit/Find tab (see Figure 7-14), and, in the Confirm section, unchecking the oddly named “Record changes” box (it’s really for deletions of individual records), the “Document deletions” box (which is really for deletion of database objects, such as tables or queries), and the “Action queries” box (actually for action queries, such as appends or deletes). Note: this turns off warning messages, but not Access error messages.

Figure 7-14. In the Confirm section of the Edit/Find tab, the “Record changes” box is for data deletion, and the “Document deletions” box is for deletion of database objects. That make sense?

If you only want to disable confirmations temporarily, just add the SetWarnings action at the beginning of your macro, with its WarningsOn property set to “No.” This will disable warnings, but not true error messages. There’s no need to turn warnings back on at the end of the macro; Access does this automatically.

If you’re writing VB code, you can do the same thing with this line: DoCmd. SetWarnings False. If you turn off warnings in code, however, they will stay disabled until you explicitly enable them again (or until you close Access).

THE ANNOYANCE: I have a macro that uses the RunApp action to download a text file of Commerce Department financial indicators from a File Transfer Protocol (FTP) site, which is then imported into a database where we run statistics on it. The problem is that Access starts the import before the FTP program finishes the download.

THE FIX: In situations like this, there’s no simple way to tell Access to wait for a process to complete. For internal processes, this isn’t a problem. For example, a macro that appends data to a table and then opens a query based on that table will automatically wait for the append to complete before opening the query. But when you’re running an external application (such as an FTP client) or waiting for user input, Access can jump the gun. We’ll discuss fixes for both scenarios, plus a way to create a generic sleep command that pauses execution for a set period of time.

To make Access wait for an external process to finish, you must add some VB code that uses a built-in Windows function called WaitForSingleObject(), which monitors an external process. Don’t worry—you don’t have to write any code yourself, or even understand how it works. The code is freely available from Access Web (go to http://www.mvps.org/access/api/api0004.htm, or search for "Shell and Wait”), thanks to Access guru Terry Kreft. Copy this code into a module. It creates a ShellWait function that’s called like this: ShellWait(”fullPathToExternalApp“), where fullPathToExternalApp is the location of the external application. To call the code from a macro, use RunCode instead of RunApp, and invoke the ShellWait function as above. To call this code from your code, of course, you’d use ShellWait directly.

Another common situation is when you want to tell a macro to pause for user input—for example, if you want to open a form, get user input, and then open a second form based on that input. Pausing the macro is not the right solution. When user input is required, a better solution is to allow the user to control the timing of events. For example, you can let the user click a command button on the first form that opens the second form when the user is ready. The Command Button Wizard makes it easy to do this. (See “Wizards” in Chapter 0.)

Occasionally, you may want your running VB code to pause for a fixed amount of time, or to yield to Windows so Windows can process user events such as keypresses or mouse clicks (so, for example, the user can cancel a long-running calculation). To pause for a fixed amount of time, the easiest solution is to use the Windows API Sleep function. To use it in your code, simply declare it in a standard module, like this:

	Declare Sub Sleep Lib "kernel32" (ByVal lngMilliseconds As Long)

Then, call it by placing the number of milliseconds of sleep you want as the argument in parentheses. For example, Sleep(250) gets you 1/4 second of sleep. Place the call wherever you want Access to sleep—the next line of code won’t run until Sleep is over. If you’re passing a variable to Sleep with the amount of time, you’ll want to wrap calls to Sleep in your own procedure and check the validity of the argument before invoking Sleep, since passing invalid arguments to the Windows API can cause your computer to crash.

To yield to Windows so that it can process queued-up events, use the DoEvents function. DoEvents is called without any arguments—it simply yields control to the operating system, and your code pauses until control is returned. Be aware that DoEvents will not return control to your code until the events queue is empty.

THE ANNOYANCE: Users get an error on our customers form if they enter a duplicate company name, but the error Access spits out is long and obscure; most users can’t figure it out. Can I create my own error messages?

THE FIX: Poorly worded and unhelpful error messages deserve our scorn—but once you learn to write a little code, you can indeed whip up your own error messages (see Figure 7-15). In one case—validation rule violations—it’s easy to replace Access’s error messages with your own, via the Validation Text property. But for all other error conditions, you’ll have to dig a bit deeper.

Figure 7-15. Error handling, before and after. The scary default error message is on top; the cuddly custom message is below.

There are two basic approaches, depending on whether the error arises in Access’s code or in your own VB code. Let’s take a look at the two cases.

	Public Const errNoDuplicates = 3022
	Public Const errNoNulls = 3314
	…
	Private Sub Form_Error(DataErr As Integer, Response As Integer)
	    Select Case DataErr
	    Case errNoDuplicates
	             MsgBox "That company name already exists.", vbExclamation
	             Response = acDataErrContinue
	    Case errNoNulls

	             MsgBox "Company name is a required field.", vbExclamation
	             Response = acDataErrContinue
	    Case Else
	             Response = acDataErrDisplay
	    End Select
	End Sub

	Private Sub cmdAddCompanyName_Click()
	    On Error GoTo ErrorHandler
	    … code to add company name
	    Exit Sub
	    ErrorHandler:
	    Select Case Err.Number
	    Case errNoDuplicates
	        MsgBox "That company name already exists.", vbExclamation
	     Case Else
	       MsgBox "There was an unexpected error. " & _
	                  Err.Description, vbExclamation, _
	                    "Error Number: " & Err.Number
	    End Select
	End Sub

THE ANNOYANCE: I need a user data entry form to save the current record before opening a report, to ensure that the report reflects the latest data. There’s just one problem: I can’t find a command that lets me specify which form record I want to save.

THE FIX: It’s quite an oversight that Access forms have Refresh, Requery, and Undo procedures, but no Save Record method. You can resort to calling the Records → Save Record menu command, like this: DoCmd.RunCommand acCmdSaveRecord. But calling the Save Record menu command is an imperfect solution, because it doesn’t let you specify which form should receive the Save Record call.

Instead, most developers use an undocumented feature of the form’s Dirty property. Access uses the Dirty property to flag whether or not the current record needs to be saved. (A “dirty” record is one that’s been changed but not yet saved.) As soon as any edit occurs, Access sets the form’s Dirty property to “True.” Once the save is completed, the Dirty property is set to “False.” However, if a record is dirty and you explicitly set its Dirty property to “False” in code, Access saves the record. Why? I don’t know, but it works. In any case, this is the standard idiom for saving a record in a form:

	If Me.Dirty Then
	   Me.Dirty = False
	End If

THE ANNOYANCE: Users tell me that data they enter in my product description form isn’t getting into the database. It’s as if they never entered it! I’m not doing anything fancy on this form; how could it lose data?

THE FIX: These symptoms suggest a known bug—and it’s a real screamer. Under certain circumstances, Access will silently discard data without any notice. Fortunately, it’s not hard to prevent, once you know why it’s happening.

When you close a form, Access automatically saves the current record if it’s dirty (i.e., if it contains unsaved changes). If the record can’t be saved because it has missing or invalid data, Access is supposed to warn you and cancel the close operation (see Figure 7-16). The only problem: this doesn’t always happen! You’ll get the warning if you close a form using the window’s close button, or File → Close, but not if you close it in code with DoCmd.Close (which is the code the Command Button Wizard generates). If Access can’t save the record, it simply closes the form without uttering a peep. Microsoft knows about this behavior (see MSKB 207657) but does not consider it a bug!

Figure 7-16. This is the standard error message Access puts up when you try to close a form whose data is invalid. But if you close the same form in VB code, Access gives no warning and simply discards the data.

The fix is to do an explicit save in code before calling DoCmd.Close (see the previous Annoyance, “Save a Record”). It’s good practice to do this whenever you’re about to move away from a dirty record, even when you know Access won’t discard the data. For instance, if you try to move from a dirty record that can’t be saved using DoCmd.GoToRecord to another record, your users will get the wonderfully unhelpful message: “You can’t go to the specified record.” That’s sure to cause some gnashing of teeth. Instead, do an explicit save. If the record can’t be saved, your users will get a message that explains why, and the opportunity to fix the problem.

THE ANNOYANCE: I’m trying to use a MsgBox in a Before Update event, and the VB Editor keeps indicating a syntax error—but my syntax follows the example exactly. Am I losing my mind?

THE FIX: This is one of those quirks that makes you want to break something. This line of code is fine:

	strResult = MsgBox("Hi there!", vbInformation)

And so is this one:

	MsgBox "Hi there!", vbInformation

But this gives a syntax error:

	MsgBox("Hi there!", vbInformation)

Why? Because Visual Basic has bizarre rules for procedure parentheses. If you’re used to programming in any other mainstream language, forget what you know. The easiest solution is to stop using parentheses; they’re completely optional anyway. Still, if you want to use them for clarity, here are the rules:

THE ANNOYANCE: I found a button in the Forms Toolbox called “More Controls.” It must have several hundred items on it, and I have no idea what any of them are. When I tried to use one, I got an error that said my OLE server was not registered. What is that about?

THE FIX: The “More Controls” button lets you use ActiveX controls (formerly known as OLE controls) in your forms and reports (see Figure 7-17). These controls are similar to Access’s built-in controls, except they’re not built in; they’re modular pieces of software that live in their own files (typically OLE Control Extension [OCX] or DLL files). Microsoft Office ships with many ActiveX controls already installed, but the technology is an especially useful way for third-party developers to supply plug-in controls.

Figure 7-17. You’ll see this list of ActiveX controls when you click the More Controls button in the Toolbox.

You can build many applications without using ActiveX controls, but there are some useful ones that you should know about. For example, a tree-view control presents data in the familiar Windows Explorer–like folder and file view. Some controls, such as the Winsock control for the Windows socket Application Programming Interface (API), don’t provide a user interface at all; they provide libraries you can call from Visual Basic. In general, to use any of these controls, you’ll have to write a fair amount of custom code.

Before you can use an ActiveX control, you must register it in the Windows Registry. The controls you see in the More Controls list are already registered—that’s how Access populates this list. Note that this is a list of all the registered controls on your system; some of these controls may not be appropriate for Access. (See MSKB 208283 for a list of Access-compatible controls.) Furthermore, some of the third-party controls that appear under More Controls may be licensed for use only by end users (not developers). To see the whole list of registered controls and track down their OCX files, select Tools → ActiveX Controls. Click the Register button to register a new control whose file you have acquired (perhaps from a vendor or a shareware site). You can also register controls using regsvr32.exe (see “Re-Register Your DLLs” in the Appendix). If you have problems registering a control, see MSKB 249873.

Before you invest a lot of time in learning how to code for an ActiveX control, be aware that the technology has versioning and license problems—many developers turn to these controls only as a last resort. (See “Calendar Controls,” later in this chapter, for a typical example.) As you can see from the next few Annoyances in this section, there are often alternatives that use API calls or pure Visual Basic code that avoid the hazards of ActiveX controls.

THE ANNOYANCE: I want to provide a handy way for users to choose values in a fixed range, so I’m trying to use the Microsoft Slider Control that’s in the Toolbox under More Controls. But its Updated event doesn’t fire when it’s moved—and the only other events that are listed for it are focus events. It must have more events than this!

THE FIX: When you use ActiveX controls in Access, the properties sheet that comes up is often incomplete. You can find the complete set of events in the VB Editor (see Figure 7-18). In Design View, open the code module for the form using the ActiveX control by choosing View.Code. The Visual Basic Editor will fire up. Above the module, you’ll see two drop-down menus. On the left is a list of the objects in your form; select the slider (Slider0). On the right, you’ll see all of its events. In this case, Scroll and Change are probably the ones you want.

Figure 7-18. Use the Object menu on the left to set the ActiveX control (here, Slider0); the Procedure menu on the right displays all of its events. These events don’t all appear in the slider’s properties sheet, but you can access them via VB code.

THE ANNOYANCE: In Access 2000, we use the ActiveX Calendar control to make it easy for users to pick dates on our events form. It works fine on every machine except the one that’s running Windows XP Pro—on that machine, it crashes the database.

THE FIX: Welcome to the ActiveX version of DLL hell. When you use an ActiveX control in an application that’s deployed on multiple machines, not only must you ensure that it’s installed and registered on every target machine, but you must deal with conflicting versions of the control. The version that’s compatible with your application may not be compatible with a user’s system. Because of these headaches, many developers avoid using ActiveX controls altogether. In the case of the Calendar control, there are numerous alternatives (see Figure 7-19).

Figure 7-19. Three different calendar controls. At the top left is DatePicker, a pop-up based entirely on native Access controls. At the top right is another pop-up, called MonthCalendar. At the bottom is Microsoft’s ActiveX Calendar Control, embedded in a form.

Brendan Kidwell’s free Date Picker (http://www.mvps.org/access/forms/frm0057.htm) is a simple calendar display built out of standard Access controls. To use it, import his calendar form and module into your database, and call his InputDateField() function with this one-liner:

	InputDateField txtDatefield, "Choose date"

The date value automatically shows up in the text box that you specify.

If you need more flexibility (for instance, if you want to display several months at a time) or want a more elegant look and feel, Stephen Lebans has written a free calendar module that avoids versioning and distribution problems by calling the Windows API directly. It’s quite a bit more involved to use than DatePicker, but there are good instructions on Stephen’s site (http://www.lebans.com/monthcalendar.htm).

Because calendar controls are so widely used, there are many other options as well. Tony Toews lists quite a few on his site, at http://www.granite.ab.ca/access/calendars.htm.

THE ANNOYANCE: I need to let users browse to and select files from a form, as part of a custom import process. But Access doesn’t have a control that does this.

THE FIX: Access should have a file chooser control (see Figure 7-20) that’s trivial to use…but no such luck. We’ll discuss three different solutions: the Windows API, the FileDialog object, and the Common Dialog control. They all require writing a bit of code. Although using the Windows API sounds intimidating, it’s really no harder than the others, and it’s a rock-solid solution.

Figure 7-20. Both the Windows API and FileDialog solutions let you give users a native Windows file chooser dialog.

	Dim strFilter As String
	Dim varFilePath As Variant

	strFilter = ahtAddFilterItem(strFilter, "Text Files (*.txt)", "*.txt")
	strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
	varFilePath = ahtCommonFileOpenSave( _
	    Filter:=strFilter, OpenFile:=True, _
	    DialogTitle:="Choose the file to be imported")

	If Not IsNull(varFilePath) Then
	    Me!txtFilePath = varFilePath
	End If

	Dim objFileDialog As Office.FileDialog
	Dim varFile As Variant

	Set objFileDialog = Application.FileDialog(msoFileDialogFilePicker)
	With objFileDialog
	     'Multiselect is the default
	     .AllowMultiSelect = False
	     .Title = "Choose the file to be imported"
	     .Filters.Clear
	     .Filters.Add "Text files", "*.txt"
	     .Filters.Add "All Files", "*.*"

	     'If user cancels, Show will be false
	     If .Show = True Then
	             Me!txtFilePath = .SelectedItems(1)
	     End If
	End With

THE ANNOYANCE: I need a quartile function for summary statistics for a sales report. If these functions can be built into Excel, why not Access?

THE FIX: Excel’s rich menu of functions (see Figure 7-21) puts Access’s function list to shame. In every category, Excel’s function list is deeper—especially for engineering and statistical analysis. Excel even has a NETWORKDAYS function that actually computes the number of working days between two dates! Imagine that!

Figure 7-21. Not only does Excel have a far deeper choice of built-in functions than Access, but Excel’s Help offers detailed documentation.

The good news is that if you’re not afraid of writing a little VB code, it’s easy to call Excel functions from Access. The bad news is that you won’t be able to use the Excel functions in some of the places you’d probably most like to, such as totals queries, report footers, and other aggregate functions. Nonetheless, being able to call these functions in Access can be useful.

To call an Excel function from Access, you must create a hidden instance of Excel and send it the function call. Be sure that you close Excel when you’re done, because loading too many instances of Excel can soak up your memory and bog things down. Open Excel once, reference it in a global variable, and use it for all your function calls. Then close it when you close your database.

If you don’t want to hack the VB code, you can either export your data to Excel and do your analysis there, or use a commercial add-in such as the $599 FMS Total Access Statistics (http://www.fmsinc.com), which provides a broad array of data analysis features.

Here’s a simple example that wraps Excel’s CEILING function (which does significant figure rounding) in a function that can be used in Access:

	Public Function xlCeiling(dblNumber As Double, _
	                    dblSignificance As Double)
	    Dim objExcel As Excel.Application
	    Set objExcel = New Excel.Application
	    xlCeiling = objExcel.Application.Ceiling(dblNumber, _
	                                       dblSignificance)
	    objExcel.Quit
	    Set objExcel = Nothing
	End Function

Here’s a more realistic solution, showing how you’d split this into parts so that you don’t have to open and close Excel every time your function is called:

	Public gObjExcel As Excel.Application

	Public Sub initXL()
	    Set gObjExcel = New Excel.Application
	End Sub

	Public Sub killXL()
	    gObjExcel.Quit
	    Set gObjExcel = Nothing
	End Sub

	Public Function xlCeiling(dblNumber As Double, _
	                    dblSignificance As Double)
	    xlCeiling = gObjExcel.Application.Ceiling(dblNumber, _
	                                        dblSignificance)
	End Function

We declare a global variable that refers to our Excel object. Before it can be used, it must be initialized—that’s done by calling initXL(). Just call it once before you need to use any Excel functions; then you can define as many different Excel functions as you need. They can all use the same global Excel object. To clean up (before closing your database), be sure to call killXL().

Many Excel functions, such as QUARTILE, accept a range of cells, or an array of values, as an argument. Here’s how you’d wrap a function like that:

	Public Function xlQuartile(ByRef arrNumbers() As Variant, _
	                                      intQuartile As Integer)
	    xlQuartile = objExcel.Application.Quartile(arrNumbers, intQuartile)
	End Function

OK, that’s not so bad. But the real question is how do you get your data into array form in Access? In a database, data is typically stored in rows, not arrays. To get you started, we wrote a version of DLookup() that returns an array rather than a single value. This version is not perfect—for instance, it doesn’t handle spaces in names exactly like Dlookup()—but it’ll give you an idea of what’s possible. For example, if you call it like this:

	aaDLookup_Array("orderAmount", "tblOrders")

it returns an array containing all the values in the orderAmount field of the tblOrders table. To restrict the result set, add criteria like this:

	aaDLookup_Array("orderAmount", "tblOrders", "orderDate > #2004-10-31#")

Here’s our version of DLookup():

	Public Function aaDLookup_Array(strField As String, _
	                               strDomain As String, _
	       Optional strCriteria As String) As Variant()
	    Dim arrResult() As Variant
	    Dim rst As ADODB.Recordset
	    Dim strSQL As String

	    'Set up SQL WHERE clause, if supplied
	    If Not strCriteria = "" Then
	    strCriteria = "WHERE " & strCriteria
	    End If
	    strSQL = "SELECT " & strField & " FROM " & _
	                     strDomain & strCriteria

	    'Loop through recordset

	    Set rst = New ADODB.Recordset
	    rst.Open strSQL, CurrentProject.Connection, _
	    adOpenStatic, adLockOptimistic

	    Dim lngCount As Long
	    With rst
	            If .RecordCount > 0 Then
	                     ReDim arrResult(.RecordCount) As Variant
	                     For lngCount = 0 To .RecordCount - 1
	                             arrResult(lngCount) = .Fields(strField)
	                             .MoveNext
	                     Next lngCount
	            End If
	            .Close
	    End With
	    Set rst = Nothing
	    aaDLookup_Array = arrResult
	End Function

THE ANNOYANCE: I’d like to click an email address in my contacts database and have it open my email program, just like a mailto: link on a web page. I tried using the Hyperlink data type, but Access thinks my email addresses are web pages.

THE FIX: Behold the Hyperlink data type—a masterpiece of convoluted implementation. When you type an email address into a hyperlink field, Access interprets it like this:

	[email protected]#http://[email protected]#

That’s because hyperlinks are always stored as multi-part text strings, delimited with the # sign. The first part is the display text, and the second part is the address that Access generates—which by default is a web address. Idiotic! (For more on working with hyperlinks in Access, see “Miscellaneous Export Annoyances” in Chapter 3.) We don’t recommend storing email addresses as Hyperlink types, because working with this format is just too awkward. Also, when hyperlinks show up in text boxes on forms, you won’t be able to click in the box to edit the data, because Access will follow the hyperlink as soon as you click it. Instead, store your addresses as simple text, and create a Send Email button (or label) on your form by putting one line of code in the button’s Click event:

	Application.FollowHyperlink "mailto:" & Me!txtEmailAddress

This code adds mailto: in front of the address found in your email text box, and then uses the FollowHyperlink method to pass the address to your email program. It’s a simple and effective solution (see Figure 7-22). Make sure you set your default email program in Windows, via the Internet Options control panel. If you need more control over the email (subject lines, CC fields, and so on), see the next Annoyance.

Figure 7-22. Storing email addresses as Hyperlink data sounds reasonable, but it doesn’t work well. As you can see from the hover text (top), Access interprets an email address as a web URL. Save yourself the headache, and just store them as text and use a command button (bottom) to hook them up to your email program.

If for some reason you have to use the Hyperlink data type, you can input email addresses into a hyperlink field on a form using Insert → Hyperlink.

To turn a web address into a mailto: link, add code such as the following:

	Private Sub txtEmail_AfterUpdate()
	    With Me.txtEmail
	            .Value = .Hyperlink.TextToDisplay & "#mailto:" & _
	                                     .Hyperlink.TextToDisplay & "#"
	    End With
	End Sub

THE ANNOYANCE: Twice a week, I need to email reports from our sales database to various departments in our company. Isn’t there some way to automate this?

THE FIX: To automate emailing from Access, you’ll need to use Visual Basic code that uses the SendObject command (or, alternatively, a macro that uses the SendObject action). In this fix, we’ll show you how; at the end, we’ll touch on using Outlook automation as an alternative.

SendObject sends email from Access using the default email program on your PC. It lets you specify Subject, Message, To, CC, and BCC fields, as well as a database object (such as a table or report) that will be included as an attachment. You can also specify whether the email is sent immediately, or shows up first in your email program for you to edit. Here’s how you might send a report from a form that has an email address on it, using the Click event of a command button:

	Private Sub cmdSendEmail_Click()
	DoCmd.SendObject ObjectType:=acSendReport, _
	                 ObjectName:="rptSales", _
	                 OutputFormat:=acFormatHTML, _
	                 To:=Me!txtEmailAddress, _
	                 Subject:=Me!txtReportName, _
	                 MessageText:="Here's this week's report.", _
	                 EditMessage:=True
	End Sub

Note that for clarity, we used the named argument syntax (Name:=Value). All the arguments are optional.

It’s common to CC an email to a list of recipients found in a query. Access doesn’t make it easy to put these pieces together—you’ll have to do it in VB code. Here’s how to create a CC string that you could feed to SendObject from a query result set. Place this code before your call to SendObject, and add the line CC:=strCC to that call just after the To argument:

	Dim rst As ADODB.Recordset
	Dim strCC As String
	Set rst = New ADODB.Recordset
	rst.Open "qryEmailList", CurrentProject.Connection, _
	                     adOpenStatic, adLockOptimistic
	With rst
	    If .RecordCount > 0 Then
	        Do Until .EOF
	          If Not IsNull(.Fields("emailAddr")) Then
	              strCC = strCC & .Fields("emailAddr") & ";"
	          End If
	          .MoveNext
	        Loop
	    End If
	    .Close
	End With
	Set rst = Nothing

SendObjects is surprisingly flexible and easy to use, but there are some things it won’t do: you can’t use it to send multiple reports in the same email or to attach files external to Access (such as PDFs), and you can’t take advantage of your email program’s address book. For such power tricks—assuming you use Outlook—you’ll need to write code to automate Outlook from within Access. As a starting point, see MSKB 209948 and 318881. You should also check out Outlook Redemption (http://www.dimastr.com/redemption) or Express ClickYes (http://www.snapfiles.com/get/clickyes.html), which get around the Outlook security warning that automation provokes in Office 2002 and later.