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



The information in this article applies to:

  • Microsoft Access 1.0
  • Microsoft Access 1.1
  • Microsoft Access 2.0
  • Microsoft Access for Windows 95 7.0
  • Microsoft Access 97

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

Modification Type:MajorLast Reviewed:5/28/2003
Keywords:kbhowto kbusage KB96463