ke***@carter5711.freeserve.co.uk (kevin carter) wrote in message news:<2f**************************@posting.google. com>...
hi
i have a table conataining several fields one of which is date
i want to be able to search the table on the date field using code.
the code below generates the query from a form, however i get an error
message "Run time Error 2001" when this code is run. Can anyone please
tell me where i have gone wrong or how to stop this error message
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qryArea")
strSQL = "SELECT DataTable.* " & _
"FROM DataTable " & _
"WHERE DataTable.Area='" & Me.cboDepartment.Value & "' "
& _
"AND DataTable.Dates>='" & Me.cboStartDate.Value & "'" &
_
"AND DataTable.Dates<='" & Me.cboEndDate.Value & "' " & _
"ORDER BY DataTable.opnumber;"
qdf.SQL = strSQL
DoCmd.OpenQuery "qryArea"
DoCmd.Close acForm, Me.Name
Set qdf = Nothing
Set db = Nothing
thanks in advance
kevin
Kevin,
This thread has everything you need in it already. Since you say you
are using a Date field, your problem stems from using single quotes
around your combobox dates rather than # symbols. In instances where
a function that returns a Date type is evaluated within the SQL
string, the #'s are not needed. Also, if possible, use date pickers.
Users love them and filling a text box from a date picker guarantees a
valid date. Maybe you could code the pickers so that they only allow
the dates that you were going to put in your combobox. But users do
like to know what dates are available for selection. I don't remember
if it's easy or not to change the background color of individual
dates.
James A. Fortune
Putting your knuckles in a line will give you a hint about which
months have 31 days. --- Anon.