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

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

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:   __________

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.
   
   

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

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.

• Like Forms!FormName!ControlName & "*" Or _ Forms!FormName!ControlName Is Null

  This criteria is the same as the QBF sample above, except that you can query using a wildcard. For example, if you enter "Jo" in a field using this criteria, the query returns every record in the field that begins with "Jo," including Johnson, Jones, Johanna, and so on, instead of returning only those records with an exact match to "Jo."

• Between Forms!FormName!StartDate And Forms!FormName!EndDate Or _ Forms!FormName!StartDate Is Null

  You can use this criteria to query a date field, using Start Date and End Date text boxes on the query form. Records whose start and end dates fall between the values you specify on the query form are returned. If you omit a Start Date value on the form, however, the query returns all records regardless of the End Date value.

• Like Forms!FormName!ControlName & "*" Or Is Null

  This criteria returns both records that match the criteria and records that are null. If the criteria are null, all the records are returned. Note that the asterisk (*) is considered a parameter, as it is part of a larger Like expression. Because the asterisk is a hard-coded criteria value (for example, Like "*") records with null values are returned.

• Like IIf(IsNull(Forms!FormName![ControlName]), _ "*",[Forms]![FormName]![ControlName])

  This criteria returns all the records that match the criteria. If no criteria are specified in the query form, all records that are NOT null are returned.

• IIf(IsNull(Forms!FormName![ControlName]), _ [FieldName],[Forms]![FormName]![ControlName])

  This returns all the records that match the criteria. If no criteria are specified in the query form, all records that are NOT null are returned (the same result as in the example above).

   ARTICLE-ID: Q100131
   TITLE     : ACC: How to Create a Parameter In() Statement