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

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.

   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)