Article ID: 210242
Article Last Modified on 6/23/2005
APPLIES TO
- Microsoft Access 2000 Standard Edition
This article was previously published under Q210242
Advanced: Requires expert coding, interoperability, and multiuser skills.
This article applies only to a Microsoft Access database (.mdb).
For a Microsoft Access 97 version of this article, see 136062.
SUMMARY
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).
NOTE: You can see a demonstration of the technique that is used in this article in the sample file Qrysmp00.exe. For information about how to obtain this sample file, please see the following article in the Microsoft Knowledge Base:
207626 ACC2000: Access 2000 Sample Queries Available in Download Center
MORE INFORMATION
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.
You can use the QBF method to specify the criteria for filtering an existing query, but this method can become complex when you add additional fields to the form. For example, one such complication arises when you do not type a value in one of the form's criteria fields.
A more flexible QBF technique is to use a Visual Basic function to dynamically create the SQL statement for a query that is deleted and re-created 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 inform the Microsoft Jet database engine about 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:
- Start Microsoft Access and open the sample database Northwind.mdb.
- Create a new, blank form based on the Orders table.
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- Add a command button to the form, and then set the following properties:
Name: cmdRunQuery
Caption: Run Query 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- View the form in Form view.
- 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
NOTE: The method in this example functions under the following conditions:
- 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.
REFERENCES
For more information about the CreateQueryDef method, click Microsoft Visual Basic Help on the Help menu, type createquerydef method in the Office Assistant or the Answer Wizard, and then click Search to view the topic.
For more information about SQL statements, click Microsoft Access Help on the Help menu, type view or modify the sql statement behind an existing query in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
For additional information about the QBF technique, click the article number below to view the article in the Microsoft Knowledge Base:
209645 ACC2000: How to Use the Query by Form (QBF) Technique
210242 ACC2000: Query by Form (QBF) Using Dynamic QueryDef
Additional query words: Qrysmp00 exe
Keywords: kbhowto kbprogramming KB210242
