Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی

Refining Your Query with Criteria

So far, you have learned how to select the fields you want and how to indicate the sort order for your query output. One of the important features of queries is the capability to limit your output by selection criteria. Access allows you to combine criteria by using any of several operators to limit the criteria for one or more fields. The operators and their meanings are covered in Table 4.1.

Table 4.1. Access Operators and Their Meanings

Operator	Meaning	Example	Result
=	Equal to	="Sales"	Finds only those records with "Sales" as the field value.
<	Less than	<100	Finds all records with values less than 100 in that field.
<=	Less than or equal to	<=100	Finds all records with values less than or equal to 100 in that field.
>	Greater than	>100	Finds all records with values greater than 100 in that field.
>=	Greater than or equal to	>=100	Finds all records with values greater than or equal to 100 in that field.
<>	Not equal to	<>"Sales"	Finds all records with values other than Sales in the field.
And	Both conditions must be true	Created by adding criteria on the same line of the query design grid to more than one field	Finds all records where the conditions in both fields are true.
Or	Either condition can be true	"CA" or "NY" or "UT"	Finds all records with the value of "CA", "NY", or "UT" in the field.
Like	Compares a string expression to a pattern	Like "Sales*"	Finds all records with the value of "Sales" at the beginning of the field.
Between	Finds a range of values	Between 5 and 10	Finds all records with the values of 510 (inclusive) in the field.
In	Same as Or	In("CA", "NY","UT")	Finds all records with the value of "CA", "NY", or "UT" in the field.
Not	Same as not equal to	Not "Sales"	Finds all records with values other than Sales in the field.
Is Null	Finds Nulls	Is Null	Finds all records where no data has been entered in the field.
Is Not Null	Finds all records not Null	Is Not Null	Finds all records where data has been entered the field.

Operator

Meaning

Example

Result

Equal to

="Sales"

Finds only those records with "Sales" as the field value.

Less than

<100

Finds all records with values less than 100 in that field.

Less than or equal to

<=100

Finds all records with values less than or equal to 100 in that field.

Greater than

>100

Finds all records with values greater than 100 in that field.

Greater than or equal to

>=100

Finds all records with values greater than or equal to 100 in that field.

Not equal to

<>"Sales"

Finds all records with values other than Sales in the field.

And

Both conditions must be true

Created by adding criteria on the same line of the query design grid to more than one field

Finds all records where the conditions in both fields are true.

Either condition can be true

"CA" or "NY" or "UT"

Finds all records with the value of "CA", "NY", or "UT" in the field.

Compares a string expression to a pattern

Like "Sales*"

Finds all records with the value of "Sales" at the beginning of the field.

Between

Finds a range of values

Between 5 and 10

Finds all records with the values of 510 (inclusive) in the field.

Same as Or

In("CA", "NY","UT")

Finds all records with the value of "CA", "NY", or "UT" in the field.

Not

Same as not equal to

Not "Sales"

Finds all records with values other than Sales in the field.

Is Null

Finds Nulls

Is Null

Finds all records where no data has been entered in the field.

Is Not Null

Finds all records not Null

Is Not Null

Finds all records where data has been entered the field.

NOTE

The asterisk (*) is a wild card. Used in the example "Like Sales*", it will return all records that begin with Sales and are followed by any remaining characters.

Criteria entered for two fields on a single line of the query design grid are considered an And, which means that both conditions need to be true for the record to appear in the query output. Entries made on separate lines of the query design grid are considered an Or, which means that either condition can be true for the record to be included in the query output. Take a look at the example in Figure 4.7; this query would output all records in which the ContactTitle field begins with either Marketing or Owner, regardless of the customer ID. It outputs the records in which the ContactTitle field begins with Sales only for the customers whose IDs begin with the letters

M through

R inclusive.

Figure 4.7. Adding AND and OR conditions to a query.

Design a query to find all the sales agents in Brazil or France. The criteria you build should look like those in Figure 4.8.

Notice that the criterion for the Country field is "Brazil" Or "France" because you want both Brazil and France to appear in the query output. The criterion for the ContactTitle field is "Sales Agent". Because the criteria for both the Country and ContactTitle fields are entered on the same line of the query design grid, both must be true for the record to appear in the query output. In other words, the customer must be in either Brazil or France and must also be a sales agent.

Modify the query so that you can output all the customers for whom the contact title begins with Sales. Try changing the criteria for the ContactTitle field to Sales. Notice that no records appear in the query output because no contact titles are just Sales. You must enter "Like Sales*" for the criteria. Now you get the Sales Agents, Sales Associates, Sales Managers, and so on. You still don't see the Assistant Sales Agents because their titles don't begin with Sales. Try changing the criteria to "Like *Sales*". Now all the Assistant Sales Agents appear.

Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources] نسخه متنی

فارسی

کردی

العربیه

اردو

Türkçe

Русский

English

Français

کانال فیلم من

تبیان من

فایلهای من

کتابخانه من

پنل پیامکی

وبلاگ من

اینجــــا یک کتابخانه دیجیتالی است

با بیش از 100000 منبع الکترونیکی رایگان به زبان فارسی ، عربی و انگلیسی