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
/ DATE / TIME / CRIME / STNO / STREETI0902150 / 02/15/2009 / 19:24 / ROBBERY / 120 / Ash St
I0907151 / 06/24/2009 / 09:30 / BURGLARY / 500 / Main St
Function / Use/Parameters / Examples… / …Returns /
DATE / Returns the current date / DATE() / 05/29/2009
DATEVALUE / Forces access to recognize a field or expression as a date. Use this when you want to build another query that does calculations or performs functions on the date field. / DATEVALUE(“01/01/2009”) / 01/01/2009
DAY / Returns the numeric day from a date field / DAY([Date]) / 15
24
FORMAT / Re-formats the a date/time (or part of it) to whatever you desire. Requires a parameter (after the comma) that specifies what you want / FORMAT([Date],"mmm") / Feb
Jun
FORMAT([Date],"mmmm") / February
June
FORMAT([Date],"ddd") / Sun
Wed
FORMAT([Date],"dddd") / Sunday
Wednesday
FORMAT([Date],"yyyy") / 2009
2009
FORMAT([Date],"yyyymmdd") / 20090215
20090624
FORMAT([Date],"ddmmmyy") / 15FEB09
24JUN09
HOUR / Extracts the hour of day from the time. / HOUR([Time]) / 19
9
IIF / Allows you to create a series of values based on criteria in your fields / IIF([Time]>=#07:00:00# and [Time]<#19:00:00#,"Day","Night”) / Night
Day
INT / Chops off decimal places from a number / INT(23.693940) / 23
LCASE / Converts a text field to lower case / LCASE([Crime]) / robbery
burglary
LEFT / Takes the specified left number of characters from a field / LEFT([CRIME],3) / ROB
BUR
LEN / Shows you the number of characters in a text field / LEN([Street]) / 6
7
MID / Returns the specified number of characters from a specified point in a text string. / MID([Crime],2,4) / OBBE
URGL
MID([Street],3,2) / h
in
MINUTE / Extracts the minute from the time / MINUTE([Time]) / 24
30
MONTH / Returns the numeric month from a date field / MONTH([Date]) / 2
6
NOW / Returns the current date and time. Useful for calculations. When used in calculations, calculates by day. / NOW() / 05/29/2009 18:39
NOW()-[Date] / 103.78
-25.22
RIGHT / Takes the specified left number of characters from a field / RIGHT([Crime],3) / ERY
ARY
ROUND / Rounds a field or calculation to a specified number of digits. / ROUND(NOW()-[Date],0) / 104
-25
ROUND(NOW()-[Date],1) / 103.8
-25.2
TRIM / Removes spaces from the beginning and end of a text string. Useful for cleaning addresses for geocoding. / TRIM(" 150 Main St ") / 150 Main St
UCASE / Turns lower case into upper case text / UCASE([Street]) / ASH ST
MAIN ST
WEEKDAY / Gives the numeric weekday based on a parameter. For 1=Sunday, the parameter is 1. For 1=Monday, the parameter is 2. / WEEKDAY([Date],1) / 1
4
WEEKDAY([Date],2) / 7
3
YEAR / Returns the year from a date field / YEAR([Date]) / 2009
2009
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