ACC: Query by Form (QBF) Using Dynamic QueryDef (95/97)

ID: Q136062

The information in this article applies to:

SUMMARY

Advanced: Requires expert coding, interoperability, and multiuser skills.

This article demonstrates how to use a form to specify the criteria for a query that is dynamically built by a Visual Basic for Applications function. This technique is called query by form (QBF).

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: A demonstration of the technique used in this article can be seen in the sample file, Qrysmp97.exe. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q182568
   TITLE     : ACC97: Microsoft Access 97 Sample Queries Available on
               MSL

MORE INFORMATION

You can use the QBF method to specify the criteria for filtering an existing query, but this method can become complex when you add more fields to the form. For example, one such complication arises when you do not enter a value in one of the form's criteria fields.

A more flexible QBF method is to use a Visual Basic function to dynamically create the SQL statement for a query that is deleted and recreated each time the function runs. This method avoids using an empty criteria field as part of the query's WHERE condition.

Creating the Sample Visual Basic Function

Concatenating (joining) SQL statements in Visual Basic requires that you "type cast" fields with special characters to instruct the Microsoft Jet database engine what field data types you are using. The following type- casting characters are used in this article:

   Field Type       Type-Casting Character
   ------------------------------------------
   Text             Single quotation mark (')
   Date             Number sign (#)
   Numeric          None

The following sample SQL statements demonstrate the use of type casting.

NOTE: In the following sample statements, 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 statements.

   Text: "Select * from [Orders] where [ShipCity] = '" & [Mycriteria] _
   & "' ;"
   Date: "Select * from [Orders] where [OrderDate] = #"& [Mycriteria] _
   & "# ;"
   Numeric: "Select * from [Orders] where [EmployeeID] = "& [Mycriteria] _
   & ";"

To create a sample function that uses the QBF technique to display the records matching the specified criteria, follow these steps:

1. Open the sample database Northwind.mdb and create a new, blank form

   based on the Orders table.

2. Add six text boxes to the form. Set the Name property of each text
   box as follows:

      Text box 1:
         Name: Customer ID
      Text box 2:
         Name: Ship City
      Text box 3:
         Name: Ship Country
      Text box 4:
         Name: Employee ID
      Text box 5:
         Name: Order Start Date
      Text box 6:
         Name: Order End Date

3. Add a command button to the form and set the following properties:

      Name: cmdRunQuery
      Caption: Run Query

4. Type the following code for the command button's OnClick event
   procedure:

      Private Sub cmdRunQuery_Click()
      Dim db As Database
      Dim QD As QueryDef
      Dim where As Variant

      Set db = CurrentDb()

      ' Delete the existing dynamic query; trap the error if the query does
      ' not exist.
      On Error Resume Next
      db.querydefs.Delete ("Dynamic_Query")
      On Error GoTo 0

      ' Note the single quotation marks surrounding the text fields [Ship
      ' Country] and [CustomerID].
      ' Note that there are no type-casting characters surrounding the
      ' numeric field [EmployeeID].

      where = Null
      where = where & " AND [ShipCountry]= '" + Me![Ship Country] + "'"
      where = where & " AND [CustomerID]= '" + Me![Customer Id] + "'"
      where = where & " AND [EmployeeID]= " + Me![Employee Id]

      'NOTE: In Microsoft Access 97, when you use the plus sign (+) in an
      'expression in which you are concatenating a variable of the numeric
      'data type, you must use parenthesis around the syntax, as in the
      'following example:
      '
      '   where = where & (" AND [EmployeeID]= " + Me![Employee Id])
      '
      'You must also use a conversion function to make sure that the proper
      'conversion (to either NULL or String) takes place.

      ' The following section evaluates the ShipCity criteria you enter.
      ' If the first or last character of the criteria is the wildcard
      ' character (*), then the function uses the "LIKE" operator in the
      ' SQL statement instead of "=". Also note the single quotation
      ' marks surrounding the text field [ShipCity].
      If Left(Me![Ship City],1) = "*" Or Right(Me![Ship City],1) = "*" Then
         where = where & " AND [ShipCity] like '" + Me![Ship City] + "'"
      Else
         where = where & " AND [ShipCity] = '" + Me![Ship City] + "'"
      End If

      ' Note the number signs (#) surrounding the date field [Order Date].
      If Not IsNull(Me![Order End Date]) Then
         where = where & " AND [OrderDate] between #" + _
         Me![Order Start Date] + "# AND #" & Me![Order End Date] & "#"
      Else
         where = where & " AND [OrderDate] >= #" + Me![Order Start Date] _
         + " #"
      End If

      ' Remove the following MsgBox line if you do not want to display the
      ' SQL statement.
      ' NOTE: The Mid function is used in the following MsgBox function to
      ' remove the word AND that follows the first Where clause. If you do
      ' not use the Mid function, the SQL statement contains the word AND
      ' at the beginning of the WHERE clause, for example:
      '
      '   Select * from Orders where AND [CustomerID] = 'CACTU'

      MsgBox "Select * from Orders " & (" where " + Mid(where, 6) & ";")
      Set QD = db.CreateQueryDef("Dynamic_Query", _
      "Select * from orders " & (" where " + Mid(where, 6) & ";"))
      DoCmd.OpenQuery "Dynamic_Query"
      End Sub

5. View the form in Form view.

6. To run the query, enter the following sample criteria in the text boxes,

   and then click the command button:

      Customer ID text box: CACTU
      Ship City text box: BU*
      Employee ID text box: 8
      Order Start Date text box: 1/1/94

NOTE: The method in this example has the following features:

REFERENCES

For more information about the CreateQueryDef method, search for "CreateQueryDef method" using the Microsoft Access 97 Help Index.

For more information about SQL, search for "SQL statements, writing and Generating," and then "Build SQL Statements That Include Variables and Controls" using the Microsoft Access 97 Help Index.

For more information about the QBF technique, search for "query by form" using the Microsoft Access 97 Help Index. Or see the following articles here in the Microsoft Knowledge Base:

   ARTICLE-ID: Q95931
   TITLE     : ACC: How to Use the Query-by-Form (QBF) Technique

   ARTICLE-ID: Q109334
   TITLE     : ACC: How to Dynamically Create a Filter

   ARTICLE-ID: Q117544
   TITLE     : ACC2: Query by Form (QBF) Using Dynamic QueryDef

Keywords          : kbusage QryHowto 
Version           : 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto

Last Reviewed: November 21, 1998