Access 2007[c] The Missing Manual

2017-07-07 02:10:07

7.2. Query Functions

By now, it may have crossed your mind that you can manipulate numbers and text in even more ambitious waysways that go beyond what the basic operators let you do. You may want to round off numbers or capitalize text. Access does include a feature that lets you take your expressions to the next level, and it's called functions .

A function's a built-in algorithm that takes some data that you supply, performs a calculation, and then returns a result. The difference between functions and the mathematical operators you've already seen is the fact that functions can perform far more complex operations. Access has a catalog with dozens of different functions, many of which perform feats you wouldn't have a hope of accomplishing on your own.

Functions come in handy in all sorts of interesting places in Access. You can use them in:

Calculated fields . To add information to your query results.

Filter conditions . To determine what records you see in a query.

Visual Basic code . The all-purpose extensibility system for Access that you'll tackle in Part Five.

As you explore the world of functions, you'll find that many are well suited to calculated fields but not filter conditions. In the following sections, you'll see exactly where each function makes most sense.

Note: Functions are a built-in part of the Access version of SQL (Section 6.2.3), which is the language it uses to perform data operations.

7.2.1. Using a Function

Whether you're using the simplest or the most complicated function, the syntax the rules for using a function in an expressionis the same. To use a function, simply enter the function name , followed by parentheses. Then, inside the parentheses, put all the information the function needs in order to perform its calculations (if any).

For a good example, consider the handy Round( ) function, which takes a fractional number and then tidies up any unwanted decimal places. Round( ) is a good way to clean up displayed values in a calculated field. You'll see why Round( ) is useful if you create an expression like this, which discounts prices by five percent:

SalePrice: [Price] * 0.95

Run a price like $43.97 through this expression, and you wind up with 41.7715 on the other sidewhich doesn't look that great on a sales tag. The Round( ) function comes in handy here. Just feed it the unrounded number and the number of decimal places you want to keep:

SalePrice: Round([Price] * 0.95, 2)

Technically, the Round() function requires two pieces of information, or arguments . The first's the number you want to round (in this case, it's the result of the calculation Price * 0.95 ), and the second's the number of digits that you want to retain to the right of the decimal place (2). The result: the calculation rounded to two decimal places, or 41.77.

Note: Most functions, like Round( ), require two or three arguments. However, some functions can accept many more, while a few don't need any arguments at all.

FREQUENTLY ASKED QUESTION

Banker's Rounding

Access doesn't seem to round numbers correctly. What's going on?

It may surprise you that Access rounds the number 21.985 to 21.98. If you were taught to always round up the number 5, you probably expect 21.99 instead. This is known as arithmetic rounding . However, Access doesn't use arithmetic roundinginstead, it chooses banker's rounding , which is better in some situations.

The difference between arithmetic rounding and banker's rounding is how they treat the number 5. Since 21.985 lies exactly halfway between 21.98 and 21.99, it isn't easy to decide what to do with it. If you always round 5 up, you'll introduce a bias in totals and averages. Because you round up more often that you round down, any total or average that you calculate ends up just a smidge higher than it should be.

Banker's rounding addresses this by rounding 5 up sometimes and down other times, depending on whether it's paired with an even or odd number. 21.985 is rounded down to 21.98, but 21.995 is rounded up to 22. This way isn't the only way to fight rounding bias (you could decide randomly when to round and when not to), but it's a commonly accepted practice in accounting and statistics.

7.2.1.1. Nested functions

You can use more than one function in a single calculated field or filter condition. The trick is nesting : nerdspeak for putting one function inside another. For example, Access provides an absolute-value function named Abs( ) that converts negative numbers to positive numbers (and leaves positive numbers unchanged). Here's an example that divides two fields and makes sure the result is positive:

Speed: Abs([DistanceTravelled] / [TimeTaken])

If you want to round this result, you place the entire expression inside the parentheses for the Round( ) function, like so:

Speed: Round (Abs([DistanceTravelled] / [TimeTaken]), 2)

When evaluating an expression with nested functions, Access evaluates the innermost function first. Here, it calculates the absolute value, and then rounds the result. In this example, you could swap the order of these steps without changing the result:

Speed: Abs(Round([DistanceTravelled] / [TimeTaken], 2))

In many other situations, the order you use is important, and different nesting produces a different result.

Nested functions can get ugly fast. Even in a relatively simple example like the speed calculation, it's difficult to tell what's going on without working through the calculation piece by piece. And if you misplace a bracket , the whole calculation can be thrown off. When you need to nest functions, it's a good idea to build them up bit by bit, and run the query each time you add another function into the mix, rather than try to type the whole shebang at once.

7.2.2. The Expression Builder

Functions are a great innovation, but Access just might have too much of a good thing. Access provides a catalog of dozens of different functions tailored for different tasks , some of which are intended for specialized mathematical or statistical operations.

Note: This book doesn't cover every Access function. (If it did, you'd be fighting to stay awake.) However, in the following sections you'll see the most useful functions for working with numbers, text, and dates. To discover even more functions, use the Expression Builder. Or, if you prefer to do your learning online, check out the pithy resource www.techonthenet.com/access/functions.

To quickly find the functions you want, Access provides a tool called the Expression Builder. To launch the Expression Builder, follow these steps:

Open a query in Design view .

Right-click the box where you want to insert your expression, and then choose Build .

If you're creating a calculated field, then you need to right-click the Field box. If you're creating a filter condition, then you need to right-click the Criteria box.

Once you choose Build, the Expression Builder appears, showing any content that's currently in the box (Figure 7-3).

Figure 7-3. The Expression Builder consists of a text box at the top of the window, where you can edit your expression, some buttons that quickly insert common operators (like +, -, /, and *, if for some reason you can't find them on the keyboard), and a three-paned browser at the bottom of the window that helps you find fields and functions you want to use.

Add or edit the expression .

The Expression Builder includes two shortcuts that you'll want to try. You can insert a name without typing it by hand (Figure 7-4), and you can find a function by browsing (Figure 7-5).

Figure 7-4. To pop in a new field name, double-click the Tables folder in the leftmost list. Then, click the subfolder that corresponds to the table you want to use. Finally, double-click the field name in the middle list to insert it into your expression. This technique's recommended only for those who love to click.

Note: The Expression Builder is an all-purpose tool to create expressions for calculated fields and filter conditions. Some options make sense only in one context. The logical operators like the equals (=) symbol and the And, Or, Not, and Like operators are useful for setting criteria for filtering (Section 6.2.1.1), but don't serve any purpose in calculated fields.

Figure 7-5. To find a function, start by double-clicking the Functions folder in the list on the left. Then, choose the Built-In Functions subfolder. (The other option shows you any custom functions that you've added to your database using custom VBA code.) Next, choose a function category in the middle list. The list on the right shows all the functions in that category. You can double-click the function to insert it into your expression.

Note: When you insert field names in the Expression builder, they're written in a slightly lengthier format that always includes the table name. You'll see [Products]![Price] instead of just [Price] . Don't worry both mean the same thing to Access.

Click OK .

Access copies your new expression back into the Field box or Criteria box.

Note: When you use the Expression Builder to add a function, it adds placeholders (like <number> and <precision>) where you need to supply the arguments. Replace this text with the values you want to use.

Most Access experts find that the Expression Builder is too clunky to be worth the trouble. But even though the Expression Builder may not be the most effective way to write an expression, it's a great way to learn about new and mysterious functions, thanks to its built-in function reference. If you find a function that sounds promising but you need more information, select it in the list and then click Help. You'll be rewarded with a brief summary that explains the purpose of the function and the arguments you need to supply, as shown in Figure 7-6.

Figure 7-6. The reference for the Round( ) function spells out what it does, and explains the two parameters. One parameterthe number of decimal placesis wrapped in square brackets, which means it's an optional value. Leave it out, and Access rounds to the nearest whole number. You'll also notice a table of contents on the left that lets you browse to any other Access function and read its description.

7.2.3. Formatting Numbers

Format( ) is one interesting mathematical function, which transforms numbers into text. Format( ) is interesting because the text it creates can be formatted in several different ways, which allows you to control exactly how your numbers are presented.

To understand the difference, think back to the expression you used earlier for discounting product prices:

SalePrice: [Price] * 0.95

Even if the Price field has the Currency data type, the calculated values in the SalePrice field appear as ordinary numbers (without the currency sign, thousands separator, and so on). So you see 43.2 when you might prefer $43.20.

You can remedy this problem by using the Format( ) function to apply a currency format:

SalePrice: Format([Price] / 0.95, "Currency")

Now the calculated values include the currency sign. Even better, since currencies are displayed with just two decimal places, you no longer need to clean up fractional values with the Round( ) function.

The trick to using the Format( ) function is knowing what text to supply for the second argument in order to get the result you want. Table 7-2 spells out your options.

Table 7-2. Formatting Options

Format	Description	Example
Currency	Displays a number with two decimal places, thousand separators, and the currency sign.	$1,433.20
Fixed	Displays a number with two decimal places.	1433.20
Standard	Displays a number with two decimal places and the thousands separator.	1,433.20
Percent	Displays a percent value (a number multiplied by 100 with a percent sign). Displays two digits to the right of the decimal place.	143320.00%
Scientific	Displays a number in scientific notation, with two decimal places.	1.43E+03
Yes/No	Displays No if the number's 0 and Yes if the number's anything else. You can also use the similar format types True/False and On/Off.	Yes

POWER USERS' CLINIC

More Advanced Number Formats

True perfectionists won't be happy with the format options in Table 7-2. Instead, they'll want complete control over the number of decimal places. One option is to use the FormatCurrency( ), FormatPercent( ), and FormatNumber( ) functions (depending on whether you want the resulting text to use currency format, percent format, or be displayed as an ordinary number). When using these functions, you pass the value that you want to format as the first argument, and the number of decimal places you want to keep in the second argument.

For even more control, you can define a custom number format that spells out exactly what you want, and use that with the Format( ) function. You won't learn about custom number formats in this book, but you can get more information on this feature in Excel 2007: The Missing Manual , or in Access Help (Figure 7-6).

7.2.4. More Mathematical Functions

The mathematical functions in Access don't get much respect, because people don't need them terribly often. You've already seen Round( ) and Format( )the most useful of the bunchbut there are still a few others that Access mavens turn to from time to time in calculated fields. They're listed in Table 7-3.

Table 7-3. Functions for Numeric Data

Function	Description	Example	Result
Sqr( )	Get the square root	Sqr(9)	3
Abs( )	Gets the absolute value (negative numbers become positive)	Abs(-6)	6
Round( )	Rounds a number to the specified number of decimal places	Round(8.89, 1)	8.9
Fix( )	Gets the integer portion of the number, chopping off any decimal places	Fix(8.89)	8
Int( )	The same as Fix( ), but negative numbers are rounded down instead of up	Int(-8.89)	-9
Rnd( )	Generates a fractional random number between 0 and 1	Int ((6) * Rnd + 1)	A random integer from 1 to 6
Val( )	Converts numeric data in a text field into a bona fide number, so that you can use it in a calculation. Stops as soon as it finds a non-numeric character, and returns 0 if it can't find any numbers.	Val("315 Crossland St")	315
Format( )	Turns a number into a formatted string, based on the options you chose	Format(243.6, Currency)	$243.60

GEM IN THE ROUGH

Use Random Numbers for a Random Sort

People rarely use the Rnd( ) functionafter all, who needs to fill a column with made-up information? However, enterprising Access gurus have come up with one intriguing use for Rnd( ). They use it to sort a table so that all the records appear in a random order.

Basically, you add a calculated field that holds the random number. You can use a field expression such as Random: Rnd( ) . If you look at your query results, then you will see a random value between 0 and 1 (like 0.7045, 0.2344, and so on) next to each record.

Now, switch back to Design view, and then clear the Show checkbox so that the Random field doesn't appear in the datasheet. Next, choose Ascending or Descending (it really doesn't matter) in the Sort box, and then rerun the query. Voil ! Every time you use this query, the records appear in a different order, according to the random numbers that Access generates on the fly.

7.2.5. Text Functions

So far, all the functions you've seen have worked with numeric data. However, there's still a lot you can do with text. Overall, there are three ways you can manipulate text:

Join text . You can do things like combining several fields together into one field. This technique doesn't require a functioninstead, you can use the & operator described in Section 7.1.3.

Extract part of a text value . You may want just the first word in a title or the first 100 characters in a description.

Change the capitalization . You may want to show lowercase text in capitals, and vice versa.

Table 7-4 shows the most common functions people use with text.

Table 7-4. Functions for Text

Function	Description	Example	Result
UCase( )	Capitalizes text	UCase("Hi There")	HI THERE
LCase( )	Puts text in lowercase	LCase("Hi There")	hi there
Left( )	Takes the number of characters you indicate from the left side	Left("Hi There", 2)	Hi
Right( )	Takes the number of characters you indicate from the right side	Right("Hi There", 5)	There
Mid( )	Takes a portion of the string starting at the position you indicate, and with the length you indicate	Mid("Hi There", 4, 2)	Th
Trim( )	Removes blank spaces from either side (or use LTrim( ) and RTrim( ) to trim spaces off just the left or right side)	Trim("Hi There")	Hi There
Len( )	Counts the number of characters in a text value	Len("Hi There")	8

Using these functions, you can create a calculated field that shows a portion of a long text value, or changes its capitalization. However, how you can use these functions in a filter expression may not be as obvious. You could create a filter condition that matches part of a text field, instead of the whole thing. Here's an example of a filter condition that selects records that start with Choco :

Left([ProductName], 5) = "Choco"

Figure 7-7 shows how you enter this filter condition.

Figure 7-7. The Left( ), Right( ), and Mid( ) functions work in much the same way as the Like keyword (Section 4.3.2.3) to help you match bits and pieces of long text values.

The Len( ) function's a bit of an oddity. It examines a text value and returns numeric information (in this case, the number of characters in the value, including all spaces, letters, numbers, and special characters). The Len( ) function isn't too useful in a simple calculated expression, because you'll rarely be interested in the number of letters in a text value. However, it does let you write some interesting filter conditions, including this one that grabs records with a Description of less than 15 characters (which probably could use some extra information):

Len(Description) < 15

7.2.6. Date Functions

You've already seen how you can use simple addition and subtraction with dates (Section 7.1.2.2). However, you can accomplish a whole lot more with some of Access's date functions.

Without a doubt, everyone's favorite date functions are Now( ) and Date( ), which you first saw in Chapter 4 (Section 4.3.2.3). These functions grab the current date and time, or just the current date. You can use these functions to create queries that work with the current year's worth of orders.

Here's a filter condition that uses Date( ) to select projects that are past due:

=<Date()

Add this to the Criteria box for the DueDate field, and you'll see only those records that have a DueDate that falls on or before today.

Date logic becomes even more powerful when paired with the DatePart( ) function, which extracts part of the information in a date. DatePart( ) can determine the month number or year, letting you ignore other details (like the day number and the time). Using DatePart( ) and Date( ), you can easily write a filter condition like this one, which selects all the orders placed in the current month:

POWER USERS' CLINIC

How to Extract the First Word from a Text Value

The string manipulation functions are designed with characters in mind. They can count letters, but they don't have any understanding of words and sentences.

One way you can get around this limitation is to use the unusual Instr( ) function, which searches for one or more characters inside a text value. (The name Instr( ) is short for "in string", because you're looking for specific characters inside a text string.) To search for the characters "he" in the text string "Hi There," you'd use Instr( ) like this:

Instr("Hi There", "he")

The result's 5, because the text "he" begins in the fifth character position. If Access can't find a match, then Instr( ) returns a result of 0. If there are multiple matches, then Instr( ) gets the first.

On its own, Instr( ) isn't terribly useful for filter conditions or calculated fields. However, you can use it in combination with other functions, like Mid( ) and Left( ), to snip out a part of a string near another letter. You could use Instr( ) to search for the first space, and take all the text before that space. In this way, you end up extracting an entire word.

Here's a slightly mind-bending calculated field that gets the first word from a ProductName field, using nested functions (Section 7.2.2). (It's split over several lines here to fit the page. When you type it in, you'll put the entire expression on one line.)

FirstWordProduct: Left([ProductName], Instr([ProductName], " " - 1))

This expression translates as "find the position of the first space, subtract one, and take that many characters from the left of the text." Run this on a field with the value Banana Cream Fudge , and you'll wind up with the truncated text Banana , which makes for an impressive party trick.

DatePart("m", [DatePlaced])=DatePart("m", Date( )) And DatePart("yyyy", [DatePlaced])=DatePart("yyyy", Date( ))

This rather lengthy expression's actually a combination of two conditions joined by the And keyword. The first condition compares the month of the current date with that of the date stored in the DatePlaced field:

DatePart("m", [DatePlaced])=DatePart("m", Date())

This expression establishes that they're the same calendar month, but you also need to make sure it's the same year:

DatePart("yyyy", [DatePlaced])=DatePart("yyyy", Date())

The trick to using DatePart( ) (and several other date functions) is understanding the concept of date components . As you can see, using the text m with the DatePart( ) functions gets the month number, and using the text yyyy extracts a four-digit year. Table 7-5 shows all your options.

Table 7-5. Date Components

Component	Description	Value for February 20, 2006 1:30 PM
yyyy	Year, in four-digit format	2006
q	Quarter, from 1 to 4	1
m	Month, from 1 to 12	2
y	Day of year, from 1 to 365 (usually)	51
d	Day, from 1 to 31	20
w	Day of week, from 1 to 7	2
ww	Week of the year, from 1 to 52	8
h	Hour, from 1 to 24	13
n	Minute, from 1 to 60	30
s	Second, from 1 to 60

WORD TO THE WISE

Calculations with Dates and Times

When using date functions, you always need to be mindful of dates that include time information. (Remember, all date values can include time information. However, you tell Access whether or not to show the time component of a date, and let people enter it by choosing the right format for you date field, as explained in Section 2.3.5. Most of the time, you'll use a format that hides any time information.)

Here's the issue: The Date( ) function returns the current date with a time value of 0. In other words, if the current date's July 4, 2008, the Date( ) function gives you the very first second of July 4, 2008the moment when the clock hit 12:00 a.m. (midnight).

If you aren't storing time values, this issue isn't important, because all your dates have a time value of 0. But consider what happens if you use the General Date format (Section 2.3.5) for your DueDate, which lets users enter both date and time information. Now the =<Date( ) filter expressions has a slightly different meaningit tells Access to match all the fields that were due on or before the first second of the current day. This filter expression doesn't match a record with a due date of 4:00 p.m. today.

In this situation, you probably want to change the filter expression to this:

<(Date( )+1)

Date( )+1 is tomorrow. In other words, this filter matches any records that have a due date that falls before the first second of tomorrow.

Incidentally, Access also has a function named Now( ) that gets the current date and time. So this filter expression matches any records that were due at the current time (of the current day) or any time and any day before that:

=<Now()

You use the date components with several date functions, including DatePart( ), DateAdd( ), and DateDiff( ). Table 7-6 has these and more useful date- related functions.

Table 7-6. Functions for Dates

Function	Description	Example	Result
Date( )	Gets the current date	Date( )	1/20/2006
Now( )	Gets the current date and time	Now( )	1/20/2006 10:16:26 PM
DatePart( )	Extracts a part of a date (like the year, month, or day number)	DatePart(#1/20/2006#, "d")	20
DateSerial( )	Converts a year, month, and day into an Access date value	DateSerial(2006, 5, 4)	5/4/2006
DateAdd( )	Offsets a date by a given interval	DateAdd ("yyyy", 2, #22/11/2006#)	22/11/2008
DateDiff( )	Measures an interval between two dates	DateDiff("w", #10/15/2006#, #1/11/2007#)	12
MonthName( )	Gets the name that corresponds to a month number (from 1 to 12)	MonthName(1)	"January"
WeekdayName( )	Gets the name that corresponds to a weekday number (from 1 to 7)	WeekdayName(1)	"Sunday"
Format( )	Converts a date into formatted text (using any of the date formats described in Section 2.3.5)	Format (#27/04/2008#, "Long Date")	"April 27, 2008"

Tip: Access has other date functions that provide part of the functionality of DatePart( ). One example's Month( ), which extracts the month number from a date. Other duplicate functions include Year( ), Day( ), Hour( ), Minute( ), and Second( ). These functions don't add any advantages, but you may see them used in other people's queries to get an equivalent result.

7.2.7. Dealing with Blank Values (Nulls)

Databases have two types of fields: required and optional. Ordinarily, fields are optional (as discussed in Section 4.1.1), which means a sloppy person can leave a lot of blank values. These blank values are called nulls , and you need to handle them carefully .

If you want to write a filter condition that catches null values, simply type this text into the criteria box:

Is Null

This condition matches any fields that are left blank. Use this on the CustomerID field in the Orders table to find any orders that aren't linked to a customer. Or ignore unlinked records by reversing the condition, like so:

Is Not Null

Sometimes, you don't want to specifically search for (or ignore) null values. Instead, you want to swap those values with something more meaningful to the task at hand. Fortunately, there's an oddly named tool for just this task: the Nz( ) function.

The Nz( ) function takes two arguments. The first's a value (usually a query field) that may contain a null value. The second parameter's the value that you want to show in the query results if Access finds a null value. Here's an example that uses Nz( ) to convert null values in the Quantity field to 0:

Nz([Quantity], 0)

Converting to 0 is actually the standard behavior of Nz( ), so you can leave off the second parameter if that's what you want:

Nz([Quantity])

At this point, you may not be terribly impressed at the prospect of changing blank values in your datasheet into zeroes. But this function's a lifesaver if you need to create calculated fields that work with values that could be null. Consider this innocent-seeming example:

OrderItemCost: [Quantity] * [Price]

This expression runs into trouble if Quantity is null. Nulls have a strange way of spreading, somewhat like an invasive fungus. If you have a null anywhere in a calculation, the result of that calculation is automatically null. In this example, that means the OrderItemCost for that record becomes null. Even worse , if the OrderItemCost enters into another calculation or a subtotal , that too becomes null. Before you know it, your valuable query data turns into a ream of empty cells .

To correct this problem, use the Nz( ) function to clean up any potential nulls in optional fields:

OrderItemCost: Nz([Quantity]) * Nz([Price])

Finally, you can use Nz( ) to supply a different value altogether. In a text field, you may choose to enter something more descriptive. Here's an example that displays the text [Not Entered] next to any record that doesn't include name information:

Name: Nz([FirstName] & [LastName], "[Not Entered]")