Working with this page:
http://support.microsoft.com/defaul...b;EN-US;q210242
I have modified it to use the OR statement so any criteria matched is outputted:
This code however does not cover everything I need the query to do.
For one, I need to have the form search more than the table its based on, I have over 60 tables that need to be searched.
Second, when the tables are searched for the criteria I need to have the query return the results "by table" so I know where the results came from (which table).
Anyone kind enough to take a stab at it?
This originally started in the general discussion forum here.
http://support.microsoft.com/defaul...b;EN-US;q210242
I have modified it to use the OR statement so any criteria matched is outputted:
Code:
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 & " OR [ShipCity] like '" + Me![Ship City] + "'"
Else
where = where & " OR [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 & " OR [OrderDate] between #" + Me![Order Start Date] + "# AND #" & Me![Order End Date] & "#"
Else
where = where & " OR [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, 5) & ";")
Set QD = db.CreateQueryDef("Dynamic_Query", "Select * from orders " & (" where " + Mid(where, 5) & ";"))
DoCmd.OpenQuery "Dynamic_Query"
End Sub
For one, I need to have the form search more than the table its based on, I have over 60 tables that need to be searched.
Second, when the tables are searched for the criteria I need to have the query return the results "by table" so I know where the results came from (which table).
Anyone kind enough to take a stab at it?
This originally started in the general discussion forum here.