How to implement Query by Form (QBF) with dynamic QueryDef in Access 2000

Back to the top

Creating the Sample Visual Basic Function

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

Text: "Select * from [Orders] where [ShipCity] = '" & [Mycriteria] _
   & "' ;"
				

Date: "Select * from [Orders] where [OrderDate] = #" & [Mycriteria] _
   & "# ;"
				

Numeric: "Select * from [Orders] where [EmployeeID] = "& [Mycriteria] _
   & ";"
				

1. Start Microsoft Access and open the sample database Northwind.mdb.
2. Create a new, blank form based on the Orders table.
3. 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
   					

4. Add a command button to the form, and then set the following properties:
   Name: cmdRunQuery
   Caption: Run Query
5. Type the following code for the command button's OnClick event procedure:

   Private Sub cmdRunQuery_Click()
   Dim db As DAO.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, 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
   					

6. View the form in Form view.
7. To run the query, type 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/97

• The And operator is used to evaluate the text box criteria. For example, if you type BONAP in the Customer ID text box and 1 in the Employee ID box, the resulting query displays records in which [CustomerID] = BONAP AND [EmployeeID] = 1.
• If you type an asterisk (*) at the beginning or end of a value in the Ship City text box, the asterisk is interpreted as a wildcard and the Like operator is used in the SQL statement. For example, if you type Sea* in the Ship City text box, the resulting query displays records for which the ShipCity is Seattle.
• If you type a criteria in the Order Start Date text box, but not in the Order End Date box, the resulting query searches for everything after the specified start date instead of searching for a range of dates.
• After you type the criteria on the form and click the command button, the query is built and run. You can also use this function to print a report based on the query.
• The SQL statement built for the dynamic query is displayed in a message box.
• If you do not type any criteria, the resulting query returns all records in the table.

Back to the top