Hi all,
I'm trying to set up a search form that runs a query.
I have it all worked out except that I need help with the code.
I used the sample from the microsoft help desk but I would like to modify it so that the user can type just a portion of customer id can be used as criteria. As it is now the user has to enter "cactu"
to display all orders for customer cactu. I would like to be able to
enter "cac" or "ctu" and get the same results understanding that
it will also return cacer or ctuexr
I think I need a like statment inside the where but just can't get it.
here is the code
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
' 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 & " 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
I'm trying to set up a search form that runs a query.
I have it all worked out except that I need help with the code.
I used the sample from the microsoft help desk but I would like to modify it so that the user can type just a portion of customer id can be used as criteria. As it is now the user has to enter "cactu"
to display all orders for customer cactu. I would like to be able to
enter "cac" or "ctu" and get the same results understanding that
it will also return cacer or ctuexr
I think I need a like statment inside the where but just can't get it.
here is the code
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
' 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 & " 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
