ch05.htm

Microsoft_Access_97_Quick_Reference.zip

				<P>
				<DD><B>2. </B>Type an open square bracket, the field name, and a close square bracket.
				An example is <B>[</B>First Name<B>]</B>.
			</DL>
			<P>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.
			<H2><FONT COLOR="#000077"><B>Expression: Using the Builder</B></FONT></H2>
			<P>You can use the <I>Expression</I> Builder to create both simple and complex expressions
			for use in tables, forms, queries, and reports. Whenever a field, value, or <I>control</I>
			takes an expression, you will see a build button with an ellipsis (...) on it or
			you can use the Build button on the toolbar.</P>
			<P>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 &quot;Expression: Operators.&quot;)
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>In the Control Source of a <I>form</I> or <I>report</I> control, cell
				on the <I>Query</I> Design <I>grid</I>, or other <I>property</I>, click the build
				button (...) to view the Expression Builder.
				<P>
				<DD><B>2. </B>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
				<I>object</I> name. Then double-click the field or control name in the second column.
				<P>
				<DD><B>3. </B>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.
				<P>
				<DD><B>4. </B>To use a <I>function</I>, 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 <TT>&lt;&lt;</TT>place holders<TT>&gt;&gt;</TT>
				where <I>arguments</I> should go. Click each placeholder and type your variables,
				use the Expression Builder items, or delete the placeholder.
				<P>
				<DD><B>5. </B>When finished with the function, choose OK.
			</DL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> If you don't know the category of a function,
			choose &lt;All&gt; 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.<BR>
			You can also use functions created in <I>VBA</I>. Double-click Functions in the first
			column and choose the second folder which is the name of your <I>database</I>. 
<HR>


			</BLOCKQUOTE>

			<P>Access checks the syntax of your <I>expression</I> 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.
			<H2><FONT COLOR="#000077"><B>Expressions: Test Values</B></FONT></H2>
			<P>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.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1.</B> In the <I>Table</I> Design <I>property</I>, <I>Query</I> Design <I>grid</I>
				cell (Field or Criteria), or <I>Control</I> Source or other property on a <I>form</I>
				or <I>report</I> control, type the expression.
				<P>
				<DD><B>2. </B>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 (&amp;).
				<P>
				<DD><B>3. </B>Display the table, query, form, or report. Access might give you an
				error message in the <I>object</I>.
				<P>
				<DD><B>4. </B>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.
				<P>
				<DD><B>5.</B> 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.
			</DL>
			<H2><FONT COLOR="#000077"><B>Functions: Financial</B></FONT></H2>
			<P>If you need to calculate payments or interest rates, you would probably create
			a calculated field on a <I>query</I> (Field row of Query Design <I>grid</I>) or a
			<I>form</I> or <I>report</I> (<I>Control</I> Source <I>property</I> of a <I>text
			box</I> for a calculated field).
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>In the Control Source of a <I>form</I> or <I>report</I> control, a
				cell on the <I>Query</I> Design <I>grid</I>, or other <I>property</I>, click the
				build button (...) to view the Expression Builder.
				<P>
				<DD><B>2. </B>To use a financial <I>function</I>, 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.
				<P>
				<DD><B>3. </B>The function shows <TT>&lt;&lt;</TT>place holders<TT>&gt;&gt;</TT>
				where <I>arguments</I> 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.
				<P>
				<DD><B>4. </B>When finished, display and then save the form, report, or query.
			</DL>
			<P>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.
			<UL>
				<LI><B>Payment--Pmt(</B>rate, nper, pv<B>)</B> The amount you need to pay on a car
				or house loan or money you would get back on a loan.
				<P>
				<LI><B>Future Value--FV(</B>rate, nper, pmt<B>)</B> The value in the future when
				you make a series of payments at one rate.
				<P>
				<LI><B>Present Value--PV(</B>rate, nper, pmt<B>)</B> The value in the present of
				a series of payments at one rate.
				<P>
				<LI><B>Internal Rate of Return--IRR(</B>values<B>)</B> The rate your money is earning.
				Unlike the other three functions, this <I>function</I> will probably be in a report's
				<I>group</I> footer or report footer.
			</UL>


			<BLOCKQUOTE>
			<P>
<HR>
<FONT COLOR="#000077"><B>CAUTION:</B></FONT> When you work with financial functions,
			make sure the <TT>rate</TT> (interest rate) is for the same time period as <TT>nper</TT>
			(number of payments). So if <I>nper</I> refers to monthly payments, you will have
			to divide the annual interest rate by 12 to get monthly interest. 
<HR>
</P>
			<P>
<HR>
<FONT COLOR="#000077"><B>NOTE:</B></FONT> These functions show <I>arguments</I> that
			are required. The arguments include <TT>rate</TT> for interest rate, <TT>nper</TT>
			for number of periods, <TT>pmt</TT> for payment amount, and <TT>pv</TT> for amount
			(present value) of loan. For more information on each function, highlight the function
			name in the expression or Expression <I>Builder</I> and press F1. 
<HR>


			</BLOCKQUOTE>

			<H2><FONT COLOR="#000077"><B>Functions: <I>IIf</I></B></FONT></H2>
			<P>The <TT>IIf</TT> <I>function</I> enables you to test an expression and return
			one result if the value is true and another if the value is false. The <I>syntax</I>
			is <TT>IIf(</TT>expr,truepart,falsepart<TT>)</TT>. You would most likely type this
			expression in the Field cell of a Query Design <I>grid</I>. An example might be <B>IIf([DueDate]&gt;Date(),&quot;Call
			Now&quot;,&quot;&quot;)</B>, which says if the value of the DueDate field is greater
			than today, show the text Call Now, otherwise show nothing (a zero-length <I>string</I>).
			<DL>
				<DD><FONT COLOR="#000077"><B>Steps</B></FONT>
				<P><B>1.</B> Click in the Control Source of a <I>form</I> or <I>report</I> control,
				cell on the <I>Query</I> Design <I>grid</I>, or other <I>property</I>.</P>
				<P>
				<DD><B>2.</B> Type <B>IIf</B>, and an open parenthesis <B>(. </B>Then, type an expression
				that will evaluate to true or false.
				<P>
				<DD><B>3. </B>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.
				<P>
				<DD><B>4. </B>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.
				<P>
				<DD><B>5. </B>Finish the <TT>IIf</TT> expression with a close parenthesis <B>)</B>.
				<P>
				<DD><B>6. </B>When finished, display and then save the form, report, or query.
			</DL>
			<H2><FONT COLOR="#000077"><B>Functions: User-Defined--Use</B></FONT></H2>
			<P>You use a user-defined <I>function</I> 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 <I>property</I> in <I>Table</I> Design <I>grid</I>, Field cell to create
			calculated field in Query Design grid, criteria in Query Design grid or Advanced
			<I>Filter</I> grid, <I>Control</I> Source property of a calculated control on a <I>form</I>
			or <I>report</I>, and property of a query field or control on a report or form.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Click in the Control Source of a <I>form</I> or <I>report</I> control,
				cell on the <I>Query</I> Design <I>grid</I>, or other <I>property</I>.
				<P>
				<DD><B>2. </B>In a Control Source property, type an equal sign. For all expressions,
				type the user-defined function, an open parenthesis, any <I>arguments</I>, and a
				close parenthesis.
				<P>
				<DD><B>3. </B>If you can't remember the function or <I>syntax</I>, click the Build
				button. Double-click the Functions folder in the first column. Double-click the folder
				with your <I>database</I> name.
				<P>Single-click the <I>module</I> name in the second column. To insert a function
				with its requested prompts, double-click the function in the third column.
			</DL>
			<H2><FONT COLOR="#000077"><B>Functions: User-Defined--View</B></FONT></H2>
			<P>If Access does not have the functions you want, you can create your own in a Visual
			Basic statement in a <I>module</I>. 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 &quot;Database: Examples&quot; in the Table
			and Database Design part of this book).
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1.</B> To view a <I>function</I> created in a module, in the <I>Database window</I>,
				highlight the module name and choose the <U>D</U>esign button.
				<P>
				<DD><B>2.</B> In the drop-down box on the right top of the window (the Procedure
				drop-down <I>list box</I>), choose one of the functions. Notice that the procedure
				beginning with the keyword <TT>Function</TT> has the function name and any <I>arguments</I>
				you need to supply inside the parentheses.
				<P>
				<DD>After some programming statements, you will see the function name repeated, an
				equal sign, and an expression. This is the definition of the function.
				<P>
				<DD><B>3.</B> Close the module window. If requested, do not save changes you made.
			</DL>
			<H2><FONT COLOR="#000077"><B>Grouping in Queries</B></FONT></H2>
			<P>You can calculate expressions based on <I>groups</I> 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 <I>grid</I>. Then you create an expression
			on a different field using an <I>aggregate function</I> such as <TT>SUM</TT>, <TT>AVG</TT>,
			<TT>COUNT</TT>, <TT>MIN</TT>, <TT>MAX</TT>, <TT>STDEV</TT>, or <TT>VAR</TT>.
			<H3><FONT COLOR="#000077"><B>Steps</B></FONT></H3>
			<DL>
				<DD><B>1. </B>Double-click each field in the Field List or enter expressions in the
				Field row of the Query Design grid.
				<P>
				<DD><B>2.</B> If the Totals button has not been clicked, click it now.
				<P>
				<DD><B>3.</B> In the field or field(s) you want to group, choose Group By in the
				Total row.
				<P>
				<DD><B>4. </B>In fields that you are going to aggregate, choose one of the <I>aggregate
				functions</I> listed earlier--such as <TT>SUM</TT>, <TT>AVG</TT>, <TT>COUNT</TT>,
				<TT>MIN</TT>, <TT>MAX</TT>, <TT>STDEV</TT>, or <TT>VAR</TT> in the Totals row.
				<P>
				<DD><B>5. </B>View the query results, save, and close the query.
			</DL>
			<P>A calculation is not stored, but rather is evaluated and displayed each time the
			query is run. You cannot update the results in <I>Datasheet view</I>, but you can
			use the results of a group calculation to update records through an <I>Update Query.
			</I>(See <I>&quot;</I>Action Query: Update Query&quot; in the Queries and Filters
			part of this book.)
			<H2><FONT COLOR="#000077"><B>Grouping in Reports</B></FONT></H2>
			<P>To calculate an expression for a <I>group</I> of records on a <I>report</I> (see