Searching Multiple Form Databases (complete newbie, really need help) (1 Viewer)

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