ACC: How to Use "<", ">", and "=" Operators in Query Parameter

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

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

This article describes a simple, non-programmatic way to enable a user to type the comparison operators "<", ">", and "=" 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

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

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

3. 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)
            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 the Third and Fourth Criteria Line criteria on the
      blank lines below that).
   
   

4. Save the query and run it. 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)