E
E.S. Easterly
Hello, This is for an Access 2000 application. What I am trying to
do is modify the "210242 ACC2000: Query by Form (QBF) Using Dynamic
QueryDef" code to do a couple of new things to be more flexible. What
I basically need is the lower pane of the query design window, in a
more user friendly format.
Here is what I was thinking.
You have a combo with the available fields as the values.
Next to it you have a multiselect list box containing the possible
criteria for that field. Each selection is "OR" the additional
selection.
The next criteria line of combos and listboxes are "AND" the previous
criteria line.
In this fashion it would allow the user to create dynamic "OR"
criteria and provide vastly increased flexibility to the application.
I have played around with the code with no luck, and searched for
something similar but suprisingly haven't been able to find anything.
Also at the bottom of the form I would like a couple of combos
determining sort order similar to the report wizard.
Hopefully I've explained it clearly enough. Maybe I'm on the wrong
track. I did attach the original QBF code below for reference.
Thanks in advance, Eric
Private Sub cmdRunQuery_Click()
Dim db As DAO.Database
Dim QD As QueryDef
Dim where As Variant
Set db = CurrentDb()
On Error Resume Next
db.querydefs.Delete ("Dynamic_Query")
On Error GoTo 0
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
do is modify the "210242 ACC2000: Query by Form (QBF) Using Dynamic
QueryDef" code to do a couple of new things to be more flexible. What
I basically need is the lower pane of the query design window, in a
more user friendly format.
Here is what I was thinking.
You have a combo with the available fields as the values.
Next to it you have a multiselect list box containing the possible
criteria for that field. Each selection is "OR" the additional
selection.
The next criteria line of combos and listboxes are "AND" the previous
criteria line.
In this fashion it would allow the user to create dynamic "OR"
criteria and provide vastly increased flexibility to the application.
I have played around with the code with no luck, and searched for
something similar but suprisingly haven't been able to find anything.
Also at the bottom of the form I would like a couple of combos
determining sort order similar to the report wizard.
Hopefully I've explained it clearly enough. Maybe I'm on the wrong
track. I did attach the original QBF code below for reference.
Thanks in advance, Eric
Private Sub cmdRunQuery_Click()
Dim db As DAO.Database
Dim QD As QueryDef
Dim where As Variant
Set db = CurrentDb()
On Error Resume Next
db.querydefs.Delete ("Dynamic_Query")
On Error GoTo 0
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