Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: How to Use <, >, and = Operators in a Query Parameter

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

View products that this article applies to.

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).

↑ Back to the top

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.

↑ Back to the top

More information

  1. Open Microsoft Access, and then create a new blank database.
  2. 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
  3. 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
  4. 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)
      Third Criteria Line: >Mid([Enter Age],2)
      Fourth Criteria Line: <leave blank>
      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).
  5. Save and then run the query. The following is sample output for each of the four types of input: 
       [Enter Age]     Output
   ------------------------------------------
   <leave blank>   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.
The following is the SQL statement for the query: 
   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)

↑ Back to the top

Keywords: KB209628, kbusage, kbinfo, kbhowto

↑ Back to the top

Article Info
Article ID : 209628
Revision : 3
Created on : 7/13/2004
Published on : 7/13/2004
Exists online : False
Views : 279