Web based School

Chapter Five
Calculations

You can use calculations and expressions throughout Access in all objects--tables, queries, forms, reports, and even macros and modules. Calculations can help you find all sorts of statistics about your data--from averages, sums, and maximums, to standard deviations and present value. You can even combine text fields together in one field. You can type calculations directly or use the Expression Builder to help you.

In a query, calculations can be done by creating a new field based on the values of other fields in the query grid. You can also create grouping types of queries with crosstab and total queries.

In forms and reports, you generally add text boxes for calculations. These text boxes, as well as everything else on the form or report, are called controls, You learn more about forms and reports in the next part of this guide, "Forms and Reports."

Blanks and Nulls in Calculations

When you run a query, form, or report with calculations, you might see a blank when you expect a value. This happens when you have a null value that is part of any expression; then, the result of the expression evaluates to null. Criteria for nulls are discussed in "Criteria: Blanks, Nulls, and Empty" in the Queries and Filters part of this guide.

Steps

1. If the expression is in a form or report, open in Design View. Double-click the control containing the expression to open the Properties sheet. Click in the Control Source property (on the Data tab).

If the expression is in a query, open the query in Design View and click in the field with the expression.

2. If necessary, press Shift+F2 to zoom and see the whole expression.

3. If your expression evaluates to a numeric value and one of your fields is null, the whole expression evaluates to null. You can use the NZ function to convert any field to another number. Click before the field name and type NZ. Enclose the field name in parentheses. For addition and subtraction, use NZ(Fieldname) to convert null values to zero. For multiplication and division, use NZ(Fieldname,1) to convert null values to 1.

4. If your expression includes any text strings, use the & operator instead of + (or the entire expression will evaluate to null when any value is null). For example, type [Field1]&" "&[Field2].

5. If you don't want extra spaces in text strings, you might need to use Is Null. The IsNull function tests if a value is null. For example, to avoid having two spaces if someone does not have a middle name, use the following expression: Name: [Firstname] & " " & IIf(IsNull([Middlename]),"",[Middlename] & " ") & [Lastname]

6. When finished, display and then save the form, report, or query.

NOTE: In a Totals Query, Access does not include null values for aggregate functions such as Sum, Count, and Average. If you want to count all records including nulls, use Count(*).

Calculated Fields: Forms and Reports--Create by Typing

You can enter a calculation in a form by typing it or using the Expression Builder. When you enter a field name that includes spaces as part of the expression, you must include square brackets around the field name. Access will automatically put brackets around field names with no spaces.

Steps

1. In Design View of a form or report, click the Text Box tool and then click the form or report and type an equal sign (=).

2. If you are including a function, include the function name with parentheses (arguments might or might not go inside parentheses).

3. If the next part of the expression is a field, include field in square brackets, as in [Fieldname].

4. If you are doing arithmetic operations, include + - * /, or if you are combining text, include the ampersand (&).

5. When finished, click outside the text box.

NOTE: Only controls that have Control Source properties can take an expression with a calculated result. Expressions can be any valid Access function or operator, values, fields, or identifiers, and must start with an equal sign.

Calculated Fields: Forms and Reports--Edit Expression

After you type an expression in a text box, you can click the text box and try to edit the expression. This is okay if the expression is short, but if the expression is long you will not be able to see the entire formula.

Steps

1. Open the form or report in Design View.

2. If the Property sheet is not displayed, right-click the text box, choose Properties, and click the Data tab.

3. Click the Control Source property and edit the expression or press Shift+F2 to zoom. When done with the zoom window, choose OK.

4. Save and close the form or report.

Calculated Fields: Queries--Create by Typing

Because you do not want to store unnecessary data in a table, you often need to do calculations in a query. To create a calculated field in a query, you use a Field cell in the Query Design grid. Calculated fields in a query are not stored to disk, but are recalculated at the query's runtime.

Steps

1. Open the query in Design view and click an unused Field cell.

2. Type the name for the field followed by a colon (:).

3. If the next part of the expression is a field, include field in square brackets, as in [Fieldname].

4. If you are doing arithmetic operations, include + - * /, or if you are combining text, include &.

5. Repeat field names and operators as much as necessary, and then click outside of the field box. If there is a syntax error, Access will give you a warning message.

NOTE: To edit the expression, click the Field cell. If necessary, press Shift+F2 to zoom to a larger window.

Calculations: Average of Values

The AVG function returns the average (mean) of the values in a number field. Unlike Excel's Average function, you have only one expression (which is a fieldname) in the parentheses rather than multiple cells.

Steps

1. In the Database window, click the table or query on which you want to base the new query and choose Query from the New Object button on the toolbar. Double click any fields in the Field List you will use for the query.

2. To find an average of one value for an entire table, create a query with just that field. Click the Totals button on the toolbar and choose Avg in the Total row.

3. To find an average of one value for a selected set of records, create a select query and choose criteria for which values you want to select. Include the number field in the Query Design grid. Uncheck the show box for all fields except the number field. Click the Totals button on the toolbar and choose Avg in the Total row.

4. To find the average for a group of values, create a query and put the field(s) you will group in a column and the numeric field to average in another column. Click the Totals button on the toolbar and choose Avg in the Total row of the numeric field. The grouping field(s) has Group By in the Total row. This query will not show the detail for each record, just the averages for the groups.

5. Save the query and click the Run button on the toolbar.

NOTE: To show the detail and the average of these groups in a report, create a report that is grouped on a field. In the report's group footer, add a text box and type =Avg([Fieldname]) where Fieldname is the numeric field to average.

Calculations: Count of Items in List

The easiest way to see a count of all records (or selected records) in a table is to look at the navigation buttons at the bottom of a table or Query window after the word of. You can also look at this number for filtered records--more specifically, you can find a count of the numbers of each item in a group.

Steps

1. From the Query tab of the Database window, choose New and choose Design View.

2. Click the Totals button on the toolbar.

3. Double-click the field you want to group by (the Total row defaults to Group By).

4. Double-click a field where every record has a value (the primary key is a good choice). Choose Count in the Total row.

5. Run the query, save, and close.

Calculations: Date

Date fields are common in databases. With these fields, you can find today's date (or a date relative to today's date such as 60 days after). You can also find the difference between two dates, or the year or month portion of the date.

Steps

1. If the expression is in a form or report, open in Design View. Double-click the control containing the expression to open the Properties sheet. Click in the Control Source property (on the Data tab).

2. If the expression is in a query, open the query in Design View and click in the field with the expression.

3. If the expression is for a field in a table, open the table in Design View. Click in the field you want to change. In the lower half of the window go to the Default Value or Validation Rule property.

4. If the expression involves more than one field in a table, open the table in Design View. Right-click the title bar of the table and choose Properties. Click in the Validation Rule box.

5. Type one of the expressions listed in the following section. When finished, save and display the table, query, form, or report.

Using Date and Time Expressions

You can create both simple and complex expressions for use in tables, forms, queries, and reports. An expression takes a set of values, symbols, operators, or identifiers and evaluates it to produce a result. The following list illustrates how and when to use date and time expressions.

Date() You enter today's date as Date(). This is often the Default property in the Table Design grid or Form Design property of a control. This also is combined with an operator such as <Date() as the Validation Rule property expression or in the criteria cell of the Query Design grid. In the Field cell of Query Design grid, Date() is often combined with other date fields as in DaysLate: Date()-[DueDate]. Use Now() if you need to include the current time.
DateDiff(interval, date1, date2) If you want to find the difference between two dates in weeks or months, use DateDiff(interval, date1, date2). Enclose the interval argument in quotes such as "ww" or "m". See the upcoming note for options on the interval. You would generally use this function in a Field cell of the Query Design grid or in the Control Source property of a calculated control on a form or report.
DateAdd(interval, number, date) If you want to find a date in the future, use DateAdd(interval, number, date) in the same places and with the same values for interval as DateDiff. Number is the number of intervals.
DatePart(interval,date) To convert a date field to the month or year portion of the date (or any other interval below), use DatePart(interval,date). When you use this function, it is often in the Field cell of Query Design grid of Total and Crosstab queries.
Format(expression,fmt) If DatePart doesn't give you enough options, you can also use Format(expression, fmt) where expression can be a date field and fmt can be the same as interval below with additional choices (y = year; yy=year with 2 digits--97 or 01; yyyy = four-digit year; m = month number; mm = month number with leading 0; mmm = month abbreviation; mmmm = full month name). You enclose the fmt value in quotes also. Format is often used in Total and Crosstab queries.

NOTE: The interval options for the DateDiff, DateAdd, and DatePart functions include yyyy (Year), q (Quarter), m (Month), y (Day of year), d (Day), w (Weekday), ww (Week), h (Hour), n (Minute), and s (Second). For formatting numbers, see also "Query: Format Field" in the Queries and Filters part of this guide, and "Format: Numbers and Dates" in the Forms and Reports part of the guide.

Calculations: First and Last Values

When you want to see the first or last value in a list, you can use the First and Last functions. You need to sort the list or the results of these functions will be arbitrary.

Steps

1. On the Query tab of the Database window, choose the New button and double-click Design View.

2. In Query Design view, click the Totals button.

3. Double-click the field to sort and choose Group By in the Total row.

4. Double-click the field to find the order, and choose First or Last in the Total row.

5. View, save, and close the query.

NOTE: You can also see First and Last values in a report. Create a report that is grouped by one field. In the Group Footer section, create a calculated control and use the First or Last functions.

Calculations: Maximum and Minimum

The Maximum and Minimum functions help you find the highest and lowest values of a field. You can also find these values by sorting a table on the field of interest Descending or Ascending and looking at the first record. Unlike the Avg or Sum functions, the field you are finding the maximum or minimum value of can be a text or date field in addition to a number data type. The following items show just the maximum (MAX) function. You can use these identically with the minimum (MIN) function.

Steps

1. From the Query tab of the Database window, choose New and choose Design View.

2. Click the Totals button on the toolbar.

3. Double-click the field(s) you want to include in your query. To find the maximum of one value for an entire table, create a query with just that field. Choose Max in the Total row.

4. To find the maximum of one value for a selected set of records, choose criteria for the values that you want to select. Include the evaluation field in the Query Design grid. Uncheck the show box for all fields except the evaluation field. In the evaluation field, choose Max in the Total row.

5. To find the maximum for a group of values, insert the field(s) you will group in a column and the evaluation field to maximize in another column Choose Max in the Total row of the numeric field. The grouping field(s) has Group By in the Total row. This query will not show the detail for each record.

6. When finished, display and then save the query.

NOTE: To show the detail and the maximum of these groups in a report, create a report that is grouped on a field. In the report's group footer, add a text box and type =Max([Fieldname]) where Fieldname is the numeric field to maximize.

Calculations: Percent

Percent means per 100. When you look for percentages, you are often dividing a field's value by the total of all values for that field. Normally when you divide, the result is first in decimal notation (for example, 0.345). When you format the number as percent, Access multiplies the number by 100 and displays a percent sign (34.5%). You can calculate a percent of the whole in a report or form.

Another use of percent is to take a percentage of a number. To do the calculation, you multiply the percent times the number (10% * 500 = 50). A common mistake is to assume that a field formatted as percent will not need the decimal point or percentage sign when you enter the number (10 is entered incorrectly instead of .10 or 10%). You can use a query, form, or report to multiply a percent. (See "Calculated Fields: Queries--Create by Typing.")

NOTE: To increase an amount by a percentage, don't forget to include the amount itself by including a "1" in the multiplier. For example, to increase prices by 5 percent, you would type [Price]*1.05. Unlike Excel, you cannot type 105% in the formula, and you must use the decimal equivalent of the number.

Steps

1. Create a text box in Form or Report view, right-click the text box, and move to the Control Source property.

2. Type =[Fieldname]/Sum([Fieldname]) where Fieldname is the value for which you want to find the percent.

3. Move to the Format property. Choose Percent from the drop-down list of choices. Type the number of decimal places you want in the Decimal Places property.

4. View, save, and close the form or report.

Calculations: Standard Deviation and Variance

Standard deviation (STDEV) and variance (VAR) give you an idea how much your data varies throughout the whole table. If you have a small variance or standard deviation compared to the absolute value of the number, then most of your values are close to the mean. If you have a large standard deviation or variance, then the value of your field is spread across many numbers. Both functions work the same way ; STDEV is used in the steps of the following task. For variance, replace STDEV with VAR. Both of these functions must be used with number (or currency) fields.

Steps

1. From the Query tab of the Database window, choose New and choose Design View.

2. Click the Totals button on the toolbar.

3. Double-click the field(s) you want to include in your query. To find the standard deviation of one value for an entire table, create a query with just that field. Choose StDev in the Total row.

4. To find the standard deviation of one value for a selected set of records, choose criteria for which values you want to select. Include the evaluation field in the Query Design grid. Uncheck the show box for all fields except the evaluation field and choose StDev in the Total row.

5. To find the standard deviation for a group of values, insert the field(s) you will group in a column and the evaluation field to check for standard deviation in another column. Choose StDev in the Total row of the numeric field. The grouping field(s) has Group By in the Total row. This query will not show the detail for each record.

6. When finished, display and then save the query.

NOTE: To show the detail and the standard deviation of these groups in a report, create a report that is grouped on a field. In the report's group footer, add a text box and type = StDev ([Fieldname]) where Fieldname is the numeric field to check for standard deviation.

Calculations: Sum of Values

The Sum function is the most used function of all. Sum will total all values in a field. You can create a query that will sum an entire table or show the sum for groups of records. In a query you cannot show both the sums and the detail of the records. For that purpose, you need to create a report.

Steps

1. From the Query tab of the Database window, choose New and choose Design View.

2. Click the Totals button on the toolbar.

3. Double-click the field(s) you want to include in your query. To find the total of one field for an entire table, create a query with just that field. Choose Sum in the Total row.

4. To find the total of one field for a selected set of records, choose criteria for which fields you want to select. Include the evaluation field in the Query Design grid. Uncheck the show box for all fields except the evaluation field and choose Sum in the Total row.

5. To find the total for a group(s) of fields, insert the field(s) you will group in a column and the evaluation field to total in another column. Choose Sum in the Total row of the numeric field. The grouping field(s) has Group By in the Total row. This query will not show the detail for each record.

NOTE: To show the detail and the total of these groups in a report, create a report that is grouped on a field. In the report's group footer, add a text box and type =Sum([Fieldname]) where Fieldname is the numeric field to maximize. You can also type =Sum([Fieldname]) in a text box in the report footer to find the total for all records displayed in the report.
To show the total of values in a form (whether you can see all values or not), add a text box to the form footer and type =Sum([Fieldname]) where Fieldname is the numeric field to maximize. Tabular forms make the most sense for this kind of calculation.

Conversion Functions

If you are using a form to prompt for user input, the form can have unbound text boxes where you cannot define a data type. The same is true for the VBA InputBox function. In these and other programming cases, there might be instances where you have to use conversion functions to convert the data type of your input. The conversion in some cases actually corresponds to the data type and length property for numbers.

Steps

1. If the expression is in a form or report, open in Design View. Double-click the control containing the expression to open the Properties sheet. Click in the Control Source property (on the Data tab).

If the expression is in a query, open the query in Design View and click in the field with the expression.

2. Position the insertion point directly before a fieldname or control name and type one of the following most common conversion functions: CCur (currency), CDate (date), CStr (text), CDbl (Number-double), CSng (Number-single), CInt (Number-integer).

3. Enclose the fieldname in parenthesis.

4. When finished, display and then save the form, report, or query.

Crosstab Query: Create

Crosstab queries enable you to summarize data by two or more variables. These queries are more compact than Total queries. The first column of the result shows one variable (called the Column Heading). The first row of the result shows another variable (called the Row Heading). The intersection of a column and row displays the summary of a third variable (called the Value) for only values that match the Row and Column heading. Within reports, you can also use pivot tables (see "Pivot Table: Create") to create the same effect.

Row and Column headings are often text or date fields, and the Value field is often a number or currency data type.

Steps

1. On the Query tab of the Database window, choose New and double-click Crosstab Query Wizard.

2. In the first step of the Crosstab Query Wizard, double-click the name of the table or query.

3. Double-click up to three fields for the Row Headings; choose Next. Double-click one field for the Column Headings.

4. Choose the value you want to summarize in the Fields list, and how you want to summarize it in the Functions list (such as Sum, Count, and so on). If you want a summary of each row, check Yes, Include Row Sums; choose Next.

5. On the last dialog box of the wizard, type a name of the query and choose Finish. View the results of the query and save it.

NOTE: When you use a date field for Row or Column Headings, you probably want to group the values into time units such as month or year. In the Query Design grid, change the Field entry from the field name to an expression using the DatePart function. DatePart("yyyy",DateField) will group by year. DatePart("m",DateField) will group by month number. You can also use the Format function.

Crosstab Query: Fix Column Headings

Sometimes the order of Crosstab Headings is not appropriate. For example, if you use the Format(DateField,"mmm") function, the months will appear in alphabetical order (Apr, Aug, Dec, Feb, and so on) rather than date order.

NOTE: The mmm indicates abbreviated month names (Apr, Feb, and so on). An mmmm would indicate full spelling for the months (January) but would still give an alphabetical order.

Steps

1. Open the Crosstab Query in Design View.

2. If the properties window is not visible, right-click the title bar and choose Properties.

3. Click the Column Headings property and type the order you want the columns to appear, separating each entry with a semicolon (;).

4. View, save, and close the query.

NOTE: Access will display all fields in the Column Headings property whether there is data there or not. If you misspell an entry, that column will appear and the correct data will not.

Expression: Operators

Most people are familiar with arithmetic operators: plus (+), minus (-), times (*), and divide by (/). There are also other expressions you will use in criteria and calculations. These include greater than (>), less than (<), greater than or equal to (>=), less than or equal to (<=), and NOT (opposite of the expression that follows). Two connectors are OR (which indicates that any of the expressions need to be true for the expression to be true), and AND (which indicates both expressions need to be true for the expression to be true). Use the ampersand (&) to connect text strings.

Arithmetic expressions are evaluated based on rules of precedence--that is, the order in which the operations in an expression are carried out. The order is determined by the type of operation, its location in the expression from left to right, and whether the operation is surrounded by parentheses. Parentheses take top priority. Then comes multiplication and division, and finally addition and subtraction. 1+2*3 would first do 2*3 (=6) and then add the 1 to equal 7. (1+2)*3 would evaluate to 9. You can type the expressions or use the expression builder. (See "Expression: Using the Builder.")

Steps

1. Open an existing or new query in Query Design View.

2. To create a calculation, click the Field row, type a name for the new calculated field and a colon (:), and then type the expression. You can also use the build icon to run the expression builder to help you build the expression.

3. To select data, click any of the criteria rows (starting with the Criteria row, then use the Or row, and then any blank rows below). Type the expression.

4. Finish building the query, view the results, and save it.

NOTE: To create an expression in a report or form, open the form or report in design view. Choose the Text Box tool on the Toolbox. Start with an equal sign (=) and then type the expression.

Expression: Refer to Controls

Referring to controls is similar to referring to field names. (See "Expression: Refer to Field Names.") If the control is on the current form or report, you can just type the control name in square brackets. However, if you are referring to another open form or report, you need to include the object type and object name.

NOTE: The form or report must be open to access the value from a control.

Steps

1. In an expression, type Forms or Reports.

2. Type an exclamation mark (also called a bang).

3. Type the name of the form or report and another exclamation mark.

4. Type the name of the control.

NOTE: If the name of the form, report, or control includes a space, type square brackets around the name. An example is Forms![Names and Addresses]![Dues Amount].

Expression: Refer to Field Names

Queries, forms, reports, and even tables can refer to field names in criteria, calculations, and properties. You might need to use one field name for another field name's criteria. For example, to find all shipped dates that were after the customer's required date, include >[Required Date] in the query's criteria row of the shipped date's field.

Steps

1. Move to the appropriate place in an expression.

2. Type an open square bracket, the field name, and a close square bracket. An example is [First Name].

Access will automatically put square brackets around field names if there is no space in the name. However, it is a good habit to include brackets around all field names.

Expression: Using the Builder

You can use the Expression Builder to create both simple and complex expressions for use in tables, forms, queries, and reports. Whenever a field, value, or control takes an expression, you will see a build button with an ellipsis (...) on it or you can use the Build button on the toolbar.

An expression takes a set of values, symbols, operators, or identifiers and evaluates it to produce a result. Often the result is a value, be it numeric, text, logical Yes/No, dates and times, and so on. (See "Expression: Operators.")

Steps

1. In the Control Source of a form or report control, cell on the Query Design grid, or other property, click the build button (...) to view the Expression Builder.

2. To find a field control name to use in the expression, double-click the first column on Tables, Queries, Forms, or Reports and choose the appropriate object name. Then double-click the field or control name in the second column.

3. To use an operator (such as plus, greater than, and so on), click the button in the Expression Builder window. If it is a rarely-used operator, double-click the Operators folder in the first column of the lower portion of the Expression Builder. Then choose a category in the second column and double-click the operator in the third column.

4. To use a function, double-click Functions in the first column and then Built-In Functions. Choose a category in the second column, and double-click the function in the third column. The function shows <<place holders>> where arguments should go. Click each placeholder and type your variables, use the Expression Builder items, or delete the placeholder.

5. When finished with the function, choose OK.

NOTE: If you don't know the category of a function, choose <All> in the second column of the Expression Builder, and click and scroll in the third column. Type the first few letters of the function to scroll down to those letters.
You can also use functions created in VBA. Double-click Functions in the first column and choose the second folder which is the name of your database.

Access checks the syntax of your expression when you close the Expression Builder to see if it violates any obvious rules; if it doesn't, it allows you to enter that expression. Just because the expression's syntax checks out doesn't mean that you've created the correct expression. But at least you have an expression template interface to speed up your work, and to take much of the drudgery out of entering the symbols and data correctly.

Expressions: Test Values

The syntax you use in an expression is important. In controls on reports and forms, an expression requires the use of a preceding equal sign, but not in queries. Text is often required to be surrounded by quotation marks. In addition to spelling function and field names correctly, make sure you enclose field names with spaces in square brackets. To test an expression, follow these steps.

Steps

1. In the Table Design property, Query Design grid cell (Field or Criteria), or Control Source or other property on a form or report control, type the expression.

2. Click outside of the box you are typing in. If you have a syntax problem, Access will often give you a message. Common errors include not enough parentheses, missing one or both of the brackets for field names, or forgetting an operator such as the ampersand (&).

3. Display the table, query, form, or report. Access might give you an error message in the object.

4. If your expression was in a field, try entering data in that field. If the expression is calculated using other fields, try entering sample data in those fields.

5. If you still have problems with an expression that includes a function, go back to Design view, double-click the function name, and press F1 to view help for that function.

Functions: Financial

If you need to calculate payments or interest rates, you would probably create a calculated field on a query (Field row of Query Design grid) or a form or report (Control Source property of a text box for a calculated field).

Steps

1. In the Control Source of a form or report control, a cell on the Query Design grid, or other property, click the build button (...) to view the Expression Builder.

2. To use a financial function, double-click Functions in the first column and then Built-In Functions. Choose Financial in the second column, and double-click one of the following functions listed in the third column.

3. The function shows <<place holders>> where arguments should go. Click each placeholder and type your variables (which include field names in brackets). You can use the Expression Builder for field names or other functions. You can also delete the placeholder.

4. When finished, display and then save the form, report, or query.

The following list includes some of the common functions you would use in the preceding Step 2. You will replace the arguments in brackets with field names or numeric values.

Payment--Pmt(rate, nper, pv) The amount you need to pay on a car or house loan or money you would get back on a loan.
Future Value--FV(rate, nper, pmt) The value in the future when you make a series of payments at one rate.
Present Value--PV(rate, nper, pmt) The value in the present of a series of payments at one rate.
Internal Rate of Return--IRR(values) The rate your money is earning. Unlike the other three functions, this function will probably be in a report's group footer or report footer.

CAUTION: When you work with financial functions, make sure the rate (interest rate) is for the same time period as nper (number of payments). So if nper refers to monthly payments, you will have to divide the annual interest rate by 12 to get monthly interest.

NOTE: These functions show arguments that are required. The arguments include rate for interest rate, nper for number of periods, pmt for payment amount, and pv for amount (present value) of loan. For more information on each function, highlight the function name in the expression or Expression Builder and press F1.

Functions: IIf

The IIf function enables you to test an expression and return one result if the value is true and another if the value is false. The syntax is IIf(expr,truepart,falsepart). You would most likely type this expression in the Field cell of a Query Design grid. An example might be IIf([DueDate]>Date(),"Call Now",""), which says if the value of the DueDate field is greater than today, show the text Call Now, otherwise show nothing (a zero-length string).

Steps

1. Click in the Control Source of a form or report control, cell on the Query Design grid, or other property.

2. Type IIf, and an open parenthesis (. Then, type an expression that will evaluate to true or false.

3. Type a comma and type what you want to display if the expression in Step 2 evaluates to true. If you want text to display, include the text in quotes.

4. Type another comma and type what you want to display if the expression in Step 2 evaluates to false. Again, if you want text to display, include the text in quotes.

5. Finish the IIf expression with a close parenthesis ).

6. When finished, display and then save the form, report, or query.

Functions: User-Defined--Use

You use a user-defined function just like you use a built-in function. You can use the function in the same places you do any other function. These include a field property in Table Design grid, Field cell to create calculated field in Query Design grid, criteria in Query Design grid or Advanced Filter grid, Control Source property of a calculated control on a form or report, and property of a query field or control on a report or form.

Steps

1. Click in the Control Source of a form or report control, cell on the Query Design grid, or other property.

2. In a Control Source property, type an equal sign. For all expressions, type the user-defined function, an open parenthesis, any arguments, and a close parenthesis.

3. If you can't remember the function or syntax, click the Build button. Double-click the Functions folder in the first column. Double-click the folder with your database name.

Single-click the module name in the second column. To insert a function with its requested prompts, double-click the function in the third column.

Functions: User-Defined--View

If Access does not have the functions you want, you can create your own in a Visual Basic statement in a module. To see examples of user-defined functions, open one of the example databases supplied with Access. The Solutions file provides the richest assortment of functions (see "Database: Examples" in the Table and Database Design part of this guide).

Steps

1. To view a function created in a module, in the Database window, highlight the module name and choose the Design button.

2. In the drop-down box on the right top of the window (the Procedure drop-down list box), choose one of the functions. Notice that the procedure beginning with the keyword Function has the function name and any arguments you need to supply inside the parentheses.

After some programming statements, you will see the function name repeated, an equal sign, and an expression. This is the definition of the function.

3. Close the module window. If requested, do not save changes you made.

Grouping in Queries

You can calculate expressions based on groups of records in a query. To do so, you must establish the grouping by entering the field and selecting Group By in the Totals row of the Query Design grid. Then you create an expression on a different field using an aggregate function such as SUM, AVG, COUNT, MIN, MAX, STDEV, or VAR.

Steps

1. Double-click each field in the Field List or enter expressions in the Field row of the Query Design grid.

2. If the Totals button has not been clicked, click it now.

3. In the field or field(s) you want to group, choose Group By in the Total row.

4. In fields that you are going to aggregate, choose one of the aggregate functions listed earlier--such as SUM, AVG, COUNT, MIN, MAX, STDEV, or VAR in the Totals row.

5. View the query results, save, and close the query.

A calculation is not stored, but rather is evaluated and displayed each time the query is run. You cannot update the results in Datasheet view, but you can use the results of a group calculation to update records through an Update Query. (See "Action Query: Update Query" in the Queries and Filters part of this guide.)

Grouping in Reports

To calculate an expression for a group of records on a report (see also "Report: Grouping" in the Forms and Reports part of this guide"), you must add a control on that report and set the Control Source property. To sum records, you would type =Sum([Fieldname]) in the Control Source property. To average records, you would type =Avg([Fieldname]). (See "Calculations: Average of Values.")

Steps

1. Open the report in Design View, click the Text Box tool in the Toolbox.

2. To sum or average a group of records, add a text box to the group header or footer.

3. To sum or average all records, add a text box to the report header or footer.

4. With the text box control selected, click the Properties button on the toolbar to open the Properties sheet. 3/21/97 V&N.

5. Enter the expression starting with an equal sign in the Control Source property box.

Or, click Build and create the expression in the Expression Builder.

Pivot Table: Create

A pivot table enables you to summarize, analyze, and manipulate data in lists and tables. When you use the PivotTable Wizard to create a pivot table, you tell Access which fields in the list you want to arrange in rows and columns. Pivot tables are called such because you can quickly rearrange the position of pivot table fields to give you a different view of the table. You start the Pivot Table Wizard when you create a new form.

One useful application of pivot tables is creating summary tables that group large categories of data, with totals displayed for each category. A pivot table provides similar information to the Crosstab query. (See "Crosstab Query: Create.")

NOTE: Access "cheats" and uses the programming in Excel to create a pivot table. To do this procedure, you need to have Excel installed.

Steps

1. On the Form tab of the Database window, choose New, and choose Pivot Table Wizard. Choose OK.

2. After you read the description of a Pivot Table, choose Next and then select the Table or Query on which to base the pivot table. Double-click any fields you want to use in your pivot table. Generally, you will click at least one text or date field and one number or currency field.

3. Access launches Excel where you define the column and row layout of the pivot table. The fields are listed as buttons on the right side of the dialog box. Drag into the DATA area the button corresponding to the data field you want to summarize. To arrange items in a field in columns with the labels across the top, drag the button for that field to the COLUMN area. To arrange items in a field in rows with labels along the side, drag the button for that field to the ROW area.

4. To change the aggregate function (count, sum, average) for the data value, double-click the field name in the DATA area, choose the desired function, and choose OK. Then choose Next and Finish.

5. Access returns and shows you the results of the Pivot table in a form. Save and close the form.

NOTE: This procedure works with small numbers of categories for rows and columns. The view on the Access form is limited to what you can see on the screen at one time (there is no Page Down). Another alternative is to highlight a table or query; choose Tools, Office Links, Analyze it with Excel; and then run the pivot table directly in Excel.

Pivot Table: Modify

Because pivot tables are devices for displaying information, you cannot manually change information in the body of the table. You can, however, change the names of the pivot table fields and items. Access doesn't allow you to duplicate names. If you enter an existing field or item name inadvertently, Access will rearrange the pivot table, moving the item with that name to the location where you typed the name. (See "Pivot Table: Create" before you complete this task.)

Steps

1. Open the pivot table form in Form view and choose the Edit Pivot Table button on the bottom of the form.

2. Choose Data, Refresh Data to refresh the link to Access so that the pivot table will recalculate with the latest data and display any new results.

3. To edit a pivot table field or item name, select that field or item in the pivot table. Type the new name and press Enter.

4. To move a field from a column to row header (or column to row), drag the gray box with the field name to the new location. If your pivot table has too many columns and a few rows, for example, you may want to switch the column and row headers.

5. When finished, choose File, Close & Return to Pivot Form on Excel's menu. View, save, and close the form.

TIP: To change additional options for a specific pivot table field, double-click the field button. Choose the options you want in the PivotTable Field dialog box; then choose OK.

Running Sum and Count of Records

A running sum keeps adding the field instead of giving you the value of the field. You can set the running sum to go over the whole report or start again with each change in grouping. If you are working on a budget, for example, you might want to see how the expenses are piling up by date.

NOTE: To count the number of records in each group or report, create a text box control. Set the Control Source property to equal 1. Then follow the next procedure to create a running sum.

Steps

1. Open a report in Design view and, if not displayed, click the Field List button to display the list of fields in the report.

2. Drag the field that you want a running sum of to the detail section. If desired, move the field's label.

3. If the Property sheet is not displayed, right-click the field control and choose Properties.

4. Move to the Running Sum property and double-click to choose Over Group to reset the number when the group changes or Over All for the entire report.

5. View, save, and close the report.

Text: Combining

When you have more than one text field that you would like to combine on a query, form, or report, use the ampersand (&) operator. If you have any constant text, enclose that in quotes. A common example for what you would type is [First Name]&" "&[Last Name].

Steps

1. Create a text box in Form or Report Design view, right-click the text box, and move to the Control Source property. Or go to the Field cell in a Query Design grid.

2. Type = in a form or report control, or the name of the column header and a colon in a query.

3. Type the field name in square brackets.

4.Type a space and an ampersand (&).

5. If you have any connecting text (including spaces), type the text in quotes. Repeat Steps 3 and 4 as often as necessary. View your object, save, and close.

Text: Displaying Parts

Sometimes you only need to use some of the characters of a text field on labels, reports, or queries. Several functions enable you to choose which portion of an expression you want.

Steps

1. Click in the Control Source of a form or report control, cell on the Query Design grid, or other property.

2. Type one of the expressions from the following section.

3. When finished, display and then save the form, report, or query.

Specifying Data with Functions

Access 97 functions can display specified portions of data within your expressions. These functions include:

Left(stringexpr,n) Takes the n number of characters from the left side of the stringexpr.
Right(stringexpr,n) Takes the n number of characters from the right side of the stringexpr.
Mid(stringexpr,start,length) Takes out the middle of the stringexpr beginning at start for length characters.
InStr(stringexpr1,stringexpr2) The simplest form of this function returns the starting position of where a stringexpr2 begins in stringexpr1.
Trim(stringexpr) Removes any spaces at the beginning and end of the string expression. To remove leading spaces, use LTrim; to remove trailing spaces, use RTrim.