ACC2: Query by Form (QBF) Using Dynamic QueryDefID: Q117544
|
Moderate: Requires basic macro, coding, and interoperability skills.
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.
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.
Field Type Type-Casting Character
------------------------------------------
Text Single quotation mark (')
Date Number sign (#)
Numeric None
Text: "Select * from [Orders] where [ship city] = '" & [Mycriteria] _
& "' ;"
Date: "Select * from [Orders] where [Order Date] = #"& [Mycriteria] _
& "# ;"
Numeric: "Select * from [Orders] where [Employee ID] = "& [Mycriteria] _
& ";"
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
Caption: Run Query
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"
Ship City text box: SE*
Employee ID text box: 1
Customer ID text box: CACTU
Order Start Date text box: 1/1/94
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:
Q95931 ACC: How to Use the Query-by-Form (QBF) Technique
You can also obtain this article through Microsoft FastTips by ordering
item number Q95931 from the FastTips Main Menu.
Keywords : kbprg MdlQry
Version : 2.0
Platform : WINDOWS
Issue type : kbhowto
Last Reviewed: April 6, 1999