Microsoft KB Archive/209628

= ACC2000: How to Use <, >, and = Operators in a Query Parameter =

Article ID: 209628

Article Last Modified on 7/13/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209628



Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).



SUMMARY
This article describes how, without having to use code, you can enable a user to type the comparison operators <, >, and = in query parameters. It is best to keep the query criteria as simple as possible. For more complex criteria, you must use Visual Basic or Access Basic code.



MORE INFORMATION
 Open Microsoft Access, and then create a new blank database.  In the Database window, under Objects, click Tables, and then click New.

Create the following new table in Design view, and then save it as Names :   Field Name: First Name Data Type: Text

Field Name: Last Name Data Type: Text

Field Name: Age Data Type: Number Field Size: Single   Open the Names table in Datasheet view, and then type the following data:   First Name   Last Name   Age Tom         Smith        35 Anne        Howard        7 Jim         Bowie        20 Sue         Thomas       44   In the Database window, under Objects, click Queries, and then click New.

Create the following new query in Design view based on the Names table, and then save it as ListNames :   Query: List Names Type: Select Query

Field: First Name Table: Names Field: Last Name Table: Names Field: Age Table: Names First Criteria Line: =Mid([Enter Age],2) Second Criteria Line: Mid([Enter Age],2) Fourth Criteria Line: Show: True Field: Expr1:Left([Enter Age],1) First Criteria Line: "=" Second Criteria Line: "<" Third Criteria Line: ">" Fourth Criteria Line: Is Null Show: False Type the criteria on successive lines (that is, type the first criteria line criteria on the Criteria line, the second criteria line criteria on the Or line, and then the third and fourth criteria line criteria on the blank lines below that). </li>  Save and then run the query. The following is sample output for each of the four types of input: <pre class="fixed_text">  [Enter Age]     Output --    Tom          Smith      35 Anne        Howard      7 Jim         Bowie      20 Sue         Thomas     44

<21            Anne         Howard      7 Jim         Bowie      20

>21            Tom          Smith      35 Sue         Thomas     44

=7             Anne         Howard      7 NOTE: The query is not designed to allow combinations of <, >, and =, such as ">=20". You must always use one of the three operators, no more, no less, or you must leave the whole parameter blank. </li></ol>

The following is the SQL statement for the query: <pre class="fixed_text">  SELECT DISTINCTROW [First Name], [Last Name], Age FROM Names WHERE (Age=Mid([Enter Age],2) AND Left([Enter Age],1)="=") OR     (Age<Mid([Enter Age],2) AND Left([Enter Age],1)="<") OR     (Age>Mid([Enter Age],2) AND Left([Enter Age],1)=">") OR     (Left([Enter Age],1) Is Null)

Additional query words: greater less than

Keywords: kbhowto kbinfo kbusage KB209628

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.