Functions & Concatenation

Functions & Concatenation

·  FullName: [LNAME] & ", " & [FNAME] & " " & [MNAME]

·  MiddleInitial: Left([MNAME],1)

·  Image: "C:\Documents and Settings\mharris1\My Documents\AccessTraining\Photos\" & [PAROLEE].[CDCNUM] & ".jpg"

·  AGE: DateDiff("yyyy",[DOB],Date())-IIf(Format([DOB],"mmdd")>Format(Date(),"mmdd"),1,0)Weekday: Weekday([LASTCHANGEDATE])

·  WeekdayName: WeekdayName(Weekday([LASTCHANGEDATE]))

·  Month: Month([LASTCHANGEDATE])

·  MonthName: MonthName(Month([LASTCHANGEDATE]))

·  Year: Year([LASTCHANGEDATE])

Better Policing with Microsoft Office

Mark Stallo & Chris Bruce

Expressions, Functions, and Calculations

As we learned when we were setting table validation rules back at the beginning of this chapter, expressions are combinations of field values, operators, and functions that produce a result. Functions are pre-programmed commands that return a value based on a calculation or other operation.

Expressions and functions greatly enhance your ability to manipulate data. We are going to look at a wide variety in this section.

»Step 1: Create a new query (Create | Other |Query Design). Add the “Incidents” table and close the “Show Table” box.

Expressions, functions, and calculations are typed in the “Field” row of the design grid, as an alternative to double-clicking on a field and adding it. The simplest expressions do nothing more than re-name an existing field. For instance, the field named “DateOfReport,” while descriptive, is fairly clunky. The readers of our reports may want something simpler.

»Step 2: In the blank “Field” row of the first column, type: Date: DateOfReport (Figure A-89).

If you switch to Datasheet View, you will see that the column that normally would have a “DateOfReport” heading now simply reads “Date.”

Figure A-89: this simple expression simply renames the “DateOfReport” field

All expressions are preceded with the name of the resulting field and a colon. We will see more examples.

More advanced expressions concatenate or combine multiple fields.

»Step 3: In the blank “Field” row of the second column, type: Address: [StNo] & " " & [Street1] (Figure A-90). Make sure you put a space in between those quotation marks.

This expression sticks the address number and street name in a single field called “Address,” with a space between them. The brackets indicate field names—if you don’t type them, Access will insert them itself. The ampersands (&) are operators that tell Access to concatenate: take the “StNo” field and a space (in quotes) and the “Street1” field.

Figure A-90: concatenation expression design and result

Now it’s time for some functions, which manipulate existing data in new ways. There are numerous functions available in Access; many are listed in Table A-8. Some of the most valuable extract parts of dates.

»Step 4: In successive blank “Field” rows, enter the following functions:

Year: Year([DateOfReport])

Month: Format([DateOfReport],"mmm")

Year-Month: Format([DateOfReport],"yyyy-mm")

Figure A-91: three functions that re-arrange or extract parts of the date…

Figure A-92: …and the results

The YEAR and FORMAT functions illustrate function syntax. Functions are always followed immediately by a set of parentheses. Inside the parentheses is the name of the field that the function applies to. If there are any arguments required for the function, these follow the field name after a comma. Some functions, like YEAR, have no arguments—just the field name that contains the date from which the year is to be extracted. Others, like FORMAT, have one argument—the nature of the format. Some functions have multiple arguments (see Table A-8).

A small number of functions, like NOW (calculates the current date and time) and DATE (calculates the current date) have nothing inside the parentheses—you just open and close them: DATE(). The parentheses are still necessary to tell Access that you’re using a function instead of, for instance, a field name called “Date.”

We will do more with date functions during the next lesson on cross-tabulation. For now, we’ll move on to another function and calculation.

»Step 5: Close this query. Save it if you want to refer back to it later.

»Step 6: Select your “Larceny Arrests at the Metropolis Mall” query and open it in Design View.

We already used filtering and aggregation to determine the most likely towns of residence for our mall thieves. Now let’s take a look at their ages. There is no “Age” field, but we can calculate the individual’s age, at the date of offense, based on his or her date of birth.

»Step 7: Select the “Town” field by clicking on the thin gray selector bar above it. Hit the DELETE key to remove it from the query.

»Step 8: In the nearest blank column to the right, type the following in the “Field” row:

Age: INT(([DateOfReport]-[DOB])/365.25)

(Look at the expression carefully to make sure you see the difference between parentheses and brackets. Parentheses follow functions and enclose their arguments; brackets surround field names.)

This expression is two calculations within a function. Let’s break it down.

[DateOfReport]-[DOB] subtracts the person’s date of birth from the date of the report. This provides the person’s age in days.

([DateOfReport]-[DOB])/365.25 takes this age in days and divides it by 365.25 to give us the age in years (the .25 accounts for leap years). The parentheses are necessary to tell Access to perform the subtraction first; otherwise, by normal order of operations, it would perform the division first.

We could go with this calculation alone, but the result would be a ridiculously precise age, down to the nanosecond (e.g., 32.78493910). The INT function strips away the decimals and gives us a straight integer. (We wouldn’t want to use a ROUND function, because someone who is 36.99999 is still considered 36 until he reaches his 37th birthday.) So we enclose the entire calculation within the opened-and-closed parentheses of the INT function to achieve our result.

»Step 9: Switch to Datasheet view and see the result. Re-arrange the order of the columns so that the age comes first, and sort ascending by this column.

Figure A-93: the “Age” calculation within our design grid, and the result

Table A-8: Common Functions in Microsoft Access

For the purposes of these examples, assume that we have a table containing an incident number, a date, a time, a crime, a street number (“STNO”) and a street. Assume that the current date is 05/29/2009. The records that we’ll use for most of the examples looks like this:

INUM

Access has never documented its functions very well, but Access 2007 has better documentation than previous versions. One way to determine the uses and syntax of a function is to use Access’s otherwise fairly useless Expression Builder. Right-click on an empty Field row in a query, choose “Build,” double-click on “Functions” and then “Built-In Functions.” On the right-hand side, select a function you’re curious about and click the “Help” button. A small browser window will open with information about that function.

Figure A-94: using the Expression Builder to learn more about functions

You can use functions and expressions in reports as well, by creating a text box and entering the expression where you would normally enter the field name. The one difference in syntax is that instead of beginning the expression with the name of the resulting field and a colon, you begin with an equals sign (=).

Figure A-95: using an expression in a report