Microsoft KB Archive/96463

= ACC: How to Use &quot;<&quot;, &quot;>&quot;, and &quot;=&quot; Operators in Query Parameter =

Article ID: 96463

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q96463



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



SUMMARY
This article describes how, without having to use code, you can enable a user to type the comparison operators &quot;<&quot;, &quot;>&quot;, and &quot;=&quot; in query parameters. It's 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
  Create the following new table and save it as Names:

     Table: 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 table in Datasheet view and enter the following data:

     First Name   Last Name   Age Tom         Smith        35 Anne        Howard        7 Jim         Bowie        20 Sue         Thomas       44   Create the following new query based on the Names table:

     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: &quot;=&quot; Second Criteria Line: &quot;<&quot; Third Criteria Line: &quot;>&quot; 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 the Third and Fourth Criteria Line criteria on the blank lines below that).   Save the query and run it. 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 &quot;<&quot;, &quot;>&quot;, and &quot;=&quot;, such as &quot;>=20&quot;. You must always use one of the three operators, no more, no less, or you must leave the whole parameter blank. </li></ol>

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)=&quot;=&quot;) OR     (Age<Mid([Enter Age],2) AND Left([Enter Age],1)=&quot;<&quot;) OR     (Age>Mid([Enter Age],2) AND Left([Enter Age],1)=&quot;>&quot;) OR     (Left([Enter Age],1) Is Null)

Additional query words: greater less than

Keywords: kbhowto kbusage KB96463

-

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

© Microsoft Corporation. All rights reserved.