Article ID: 117544
Article Last Modified on 7/8/2002
APPLIES TO
- Microsoft Access 2.0 Standard Edition
This article was previously published under Q117544
Moderate: Requires basic macro, coding, and interoperability skills.
SUMMARY
This article demonstrates how to use a form to specify the criteria for a query that is dynamically built by an Access Basic function. This technique is called query by form (QBF).
This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Building Applications" manual.
MORE INFORMATION
You can use the QBF method to specify the criteria for filtering an existing query, but this method becomes complex as 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 an Access Basic function to dynamically create the SQL statement for a query that is deleted and recreated each time the function runs. This method does not use an empty criteria field as part of the query's WHERE condition.
Creating the Sample Function
Concatenating (joining) SQL statements in Access 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 [ship city] = '" & [Mycriteria] _
& "' ;"
Date: "Select * from [Orders] where [Order Date] = #"& [Mycriteria] _
& "# ;"
Numeric: "Select * from [Orders] where [Employee ID] = "& [Mycriteria] _
& ";"
To create a sample function that uses the QBF technique to display the records matching the specified criteria, follow these steps:
- Open the sample database NWIND.MDB and create a new blank form.
Add six text boxes to the form. Set the text boxes' Name property 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 set the command button's Caption property as follows:
Caption: Run Query
Type the following code for the command button's OnClick event procedure.
NOTE: In the following sample code, 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 this code.
Dim db As Database Dim QD As QueryDef Dim MyWhere As Variant Set db = dbengine.workspaces(0).databases(0) ' 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 [Customer ID]. ' Note that there are no type-casting characters surrounding the _ ' numeric field [Employee Id]. MyWhere = Null MyWhere = MyWhere & (" AND [Ship Country]= '" + _ Me![Ship Country] + "'") MyWhere = MyWhere & (" AND [Customer Id]= '" + _ Me![customer id] + "'") MyWhere = MyWhere & (" AND [Employee Id]= " + Me![Employee Id]) ' The following section evaluates the Ship City criterion you enter. ' If the first or the last character of the criterion 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 [Ship City]. If Left(Me![Ship City],1) = "*" Or Right(Me![Ship City],1) = "*" Then MyWhere = MyWhere & (" AND [Ship City] like '" + Me![Ship City] _ + "'") Else MyWhere = MyWhere & (" AND [Ship City] = '" + Me![Ship City]+"'") End If ' Note the number signs (#) surrounding the date field [Order Date]. If Not IsNull(Me![order start date]) And _ Not IsNull(Me![order end date]) Then MyWhere = MyWhere & (" AND [order date] between #" & _ Me![order start date] + "# AND #" + Me![order end date] + "#") ElseIf IsNull(Me![order end date]) Then MyWhere = MyWhere & (" AND [order date] >= #" + _ Me![order start date] + " #") ElseIf IsNull(Me![order start date]) Then MyWhere = MyWhere & (" AND [order date] <= #" + _ Me![order end date] + " #") End If ' Remove the following MsgBox line if you do not want to display the ' SQL statement. MsgBox "Select * from orders " & (" where " + Mid(MyWhere, 6) + ";") Set QD = db.CreateQueryDef("Dynamic_Query", "Select * from _ orders " & (" where " + Mid(MyWhere, 6) + ";")) DoCmd OpenQuery "Dynamic_Query"- View the form in Form view.
To run the query, enter the following sample criteria in the text boxes, and then click the command button:
Ship City text box: SE* Employee ID text box: 1 Customer ID text box: CACTU Order Start Date text box: 1/1/94
NOTE: The method in this example has the following features:
- The AND operator is used to evaluate the text box criteria. For example, if you enter "BONAP" in the Customer ID text box and "1" in the Employee ID box, the resulting query displays records in which [Customer ID] = BONAP AND [Employee ID] = 1.
- If you enter 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 enter "Sea*" in the Ship City text box, the resulting query displays records in which the Ship City is Seattle.
- If you enter 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 enter 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 that is built for the dynamic query is displayed in a message box.
- If you do not enter any criteria, the resulting query returns all the records in the table.
REFERENCES
For more information about the CreateQueryDef method, search for "CreateQueryDef," and then "CreateQueryDef Method (Data Access)" using the Microsoft Access Help menu.
For more information about SQL, search for "SQL: basics," and then "Retrieving Data Using SQL" using the Microsoft Access Help menu.
For more information about the QBF technique, search for "QBF," and then "Query by Form" using the Microsoft Access Help menu. Or, please see the following articles in the Microsoft Knowledge Base:
95931 ACC: How to Use the Query-by-Form (QBF) Technique
Keywords: kbhowto kbprogramming KB117544
