However, i am using ms access 2003. I intend to have three text boxes (VIN, Begin Date, End date) on the Query form, such that if i only have the VIN the query will return it if it is in the record, or alternatively by dates.
Please inspect the attached database for me. The code behind the search button is as follows (i left comments as originally provided):
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
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 [VIN]= '" + Me![VIN] + "'"
where = where & " AND [Date of theft]= '" + Me![Begin date] + "'"
where = where & " AND [Date of theft]= " + Me![End Date]
'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
' 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![VIN], 1) = "*" Or Right(Me![VIN], 1) = "*" Then
where = where & " AND [VIN] like '" + Me![VIN] + "'"
where = where & " AND [VIN] = '" + Me![VIN] + "'"
' Note the number signs (#) surrounding the date field [Order Date].
If Not IsNull(Me![Begin date]) Then
where = where & " AND [Date of theft] between #" +
Me![Begin date] + "# AND #" & Me![End Date] & "#"
where = where & " AND [Date of theft] >= #" + Me![Begin date] + " #"
' 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) & ";"))