ACC: How to Use the Query-by-Form (QBF) Technique

ID: Q95931


The information in this article applies to:


SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article describes how to use a form to specify the criteria for a query in Microsoft Access. This technique is called query by form (QBF).


MORE INFORMATION

You use the QBF technique to create a query form in which you enter query criteria. The query form contains blank text boxes, each representing a field in a table that you want to query. You make entries in only the text boxes for which you want to specify search criteria.

The query form is usually similar to a data entry form, as in the following sample query form:


   First Name: __________
   Last Name:  __________
   City:       __________
   State:      __________
   Zip Code:   __________ 


You can enter any combination of search criteria in the form. You can specify a City only, or a City and a State, or a Zip Code only, or any other combination of values. Fields that you leave blank on the form are ignored (no search criteria are applied to those fields). When you click a Search button on the form, it runs a query that uses the search criteria from your form.

To create a query form, follow these steps:
  1. Open the sample database Northwind.mdb (or Nwind.mdb in version 1.x or 2.0).


  2. Create the following new form not based on any table or query and save it as QBF_Form:
    
          Form: QBF_Form
          ---------------------------------
          Text box:
             Control Name: What Customer ID?
          Text box:
             Control Name: What Employee ID?
          Command button:
             Caption: Search
             OnClick (or OnPush in version 1.x): QBF_Macro 


  3. Create the following new macro, and then save it as QBF_Macro:
    
          Macro: QBF_Macro
          ------------------------
          OpenQuery
             Query Name: QBF_Query
             View: Datasheet
             Data Mode: Edit 


  4. Create the following new query based on the Orders table, and then save it as QBF_Query:

    NOTE: In the following sample criteria, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating these criteria.
    
          Query: QBF_Query
          ---------------------------------------------------------
          Field: CustomerID
             Sort: Ascending
             Criteria: Forms![QBF_Form]![What Customer ID?] Or _
                       Forms![QBF_Form]![What Customer ID?] Is Null
          Field: EmployeeID
             Sort: Ascending
             Criteria: Forms![QBF_Form]![What Employee ID?] Or _
                       Forms![QBF_Form]![What Employee ID?] Is Null
          Field: OrderID
          Field: OrderDate 


  5. View the QBF_Form in Form view. Enter the following combinations of criteria, clicking the Search button after each combination:
    
          Customer ID   Employee ID  Result
          -----------------------------------------------------------------
          <blank>       <blank>      All 830 orders in Microsoft Access 97
                                     and 7.0; 1078 orders in earlier versions.
    
          AROUT         <blank>      13 orders for AROUT in Microsoft Access 97
                                     and 7.0; 14 orders in earlier versions.
    
          AROUT         4            4 AROUT orders for employee 4.
    
          <blank>       4            156 orders for employee 4 in Microsoft
                                     Access 97 and 7.0; 209 in earlier
                                     versions. 


After you view the result set for each query, close the Datasheet window before beginning your next search. Each time you click the Search button, the parameters in the QBF query filter the data according to the search criteria specified on the QBF query form.

Notes on the QBF Parameter Criteria

The sample QBF query above implements a criteria in the query as

   Forms!FormName!ControlName Or Forms!FormName!ControlName Is Null 

to filter the data. This criteria returns all matching records. If a criteria is null, all the records are returned for the specified field.

You can specify any of the following alternative criteria to return slightly different results:

NOTE: In the following sample criteria, an underscore (_) is used as a line-continuation character. Remove the underscore from the end of the line when re-creating these criteria.




For more information about parameter queries, please see the following article in the Microsoft Knowledge Base:

Q100131 ACC: How to Create a Parameter In() Statement

Additional query words: qbe query by example


Keywords          : QryParm 
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: March 19, 1999