Criteria can be set for fields that contain numeric data. When you design your table, you designate fields that will contain numbers, currency, or dates with a specific data type so that mathematical calculations can be performed.
Activity 2.13. Specifying Numeric Criteria in a Query
In this activity, you will specify criteria in the query so that only the records of donors who pledged $25 display.
1.
Be sure the Queries object button is selected, and then double-click Create query in Design view. From the Show Table dialog box, add Pledges, and then Close the Show Table dialog box.
2.
Adjust the Query window to increase the size of the table area, and then expand the Pledges field list so all of the fields display.
3.
From the Pledges field list, use any technique you have practiced to add the following fields to the design grid: Club Affiliation, Donor Last Name, Donor Phone, and Pledge Amount.
[Page 996]
4.
Click in the Sort row under Club Affiliation, click the list arrow, and then click Ascending. In the Criteria row, under Pledge Amount, type 25 and then compare your screen with Figure 2.26.
Figure 2.26.
When entering currency values as criteria in the design grid, do not type the dollar sign, and include a decimal point only if you are looking for a specific amount that includes centsfor example 25.50.
5.
Click the Run button to view the results.
Nine pledges were made for the amount of $25.
6.
On the Query Datasheet toolbar, click the View button . Leave the query open in Design view for the next activity.
Activity 2.14. Using Comparison Operators
Comparison operators are symbols that evaluate each field value to determine if it is the same (=), greater than (>), less than (<), or in between a range of values as specified by the criteria.
If no comparison operator is specified, equal (=) is assumed. For example, in Activity 2.13, you created a query to display only records where the Pledge Amount was 25. The comparison operator of = was assumed, and Access displayed only records that had entries equal to the number specified25.
[Page 997]
In this activity, you will specify criteria in the query to display records from the Pledges table that have a pledge that is greater than or less than $25.
1.
Be sure your query from the last activity is displayed in Design view. In the Criteria row, under Pledge Amount, delete the existing criteria, type >25 and then press . Compare your screen with Figure 2.27.
Figure 2.27.
Unlike a text field, Access does not add quote marks around criteria entered in number or currency data type fields.
2.
Click the Run button .
Eight records match the criteria for a pledge amount that is greater than $25.
3.
On the Query Datasheet toolbar, click the View button . In the Criteria row, under Pledge Amount, delete the existing criteria and type <25 and then click the Run button . Compare your screen with Figure 2.28.
Figure 2.28.
(This item is displayed on page 998 in the print version)
Thirteen records display and each has a pledge amount less than $25. Notice that the results show the records for which the Pledge Amount is less than $25, but not equal to $25.
[Page 998]
4.
Switch to Design view . In the Criteria row, under Pledge Amount, delete the existing criteria and type >=25 and compare your screen with Figure 2.29.
Figure 2.29.
[Page 999]
NotePressing Enter After Criteria Is Added
If you press or click in another column or row in the query design grid after you have added your criteria, you can see how Access alters the criteria so it can interpret what you have typed. Sometimes, there is no change, such as when a number is added to a number or currency field. Other times, Access may capitalize a letter or add quote marks or other symbols to clarify the criteria. Whether or not you press after criteria is added does not affect the query results. It is used in this text to help you see how the program behaves.
5.
On the Query Design toolbar, click the Run button , and then compare your screen with Figure 2.30.
Figure 2.30.
Seventeen records display, including the records for donors who pledged exactly $25. Thus, the displayed records include pledges equal to or greater than $25. In this manner, comparison operators can be combined.
6.
At the far right of the menu bar, click the Close Window button . Click Yes to save changes to the query. Then in the Save As dialog box and using your own name, type 2A Pledges $25 or More Firstname Lastname as the query name. Click OK or press to display the Database window.
[Page 1000]
Activity 2.15. Using the Between . . . And Comparison Operator
The Between . . . And operator is a comparison operator that looks for values within a range. It is particularly useful when you need to locate records that are within a range of dates, such as the pledges collected between October 14 and October 20.
1.
Be sure the Queries object button is selected. Click your 2A Pledges $25 or More query, and then on the Database window toolbar, click Design . From the Pledges field list, double-click Date Collected to add this field to the design grid.
2.
On the Criteria row, under Pledge Amount, delete the existing criteria. On the Criteria row, under Date Collected type Between 10/14/07 And 10/20/07 and then press . Compare your screen with Figure 2.31, which has been widened to fully display the criteria.
Figure 2.31.
This criteria instructs Access to look for values in the Date Collected field that begin with 10/14/07 and end with 10/20/07. Both the beginning and ending dates will be included in the query results. Access places # symbols around both dates, which identifies these values as dates. If you type the operators between . . . and, using lowercase letters, Access will capitalize the first letter of each operator.
NoteWidening Columns in the Query Grid
Widen a column in the design grid using the same techniques that are used in a table. In the selection bar at the top of the column, point to the right border and double-click to expand the column to fully display the contents on the criteria row. You can also drag the right border to the width you want.
[Page 1001]
3.
Run the query and notice that nine pledges were collected between the dates you specified in your criteria.
4.
From the File menu, click Save As. In the Save As dialog box, type 2A Collections from 10/14 through 10/20 Firstname Lastname as the query name, and then click OK. Close the Query window.