date query

[kevin carter]

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

[Pieter Linden]

Shouldn't your date delimiter be # instead of a single quote?

[Tom van Stiphout]

On 6 Jul 2004 10:44:29 -0700, ke***@carter5711.freeserve.co.uk (kevin
carter) wrote:

To debug, I would set a breakpoint after strSQL is concatenated, and
paste the resulting string in a new query. Try to run that query, and
the parser may point out some problems.

One likely problem is that you didn't wrap the date fields with
#-signs.

To stop errors: On Error Resume Next :-)

-Tom.

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

[James Fortune]

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.

[Kevin Carter]

Tom van Stiphout <no*************@cox.net> wrote in message news:<2n********************************@4ax.com>. ..

On 6 Jul 2004 10:44:29 -0700, ke***@carter5711.freeserve.co.uk (kevin
carter) wrote:

To debug, I would set a breakpoint after strSQL is concatenated, and
paste the resulting string in a new query. Try to run that query, and
the parser may point out some problems.

One likely problem is that you didn't wrap the date fields with
#-signs.

To stop errors: On Error Resume Next :-)

-Tom.

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

thanks both for your reply , your suggestions resolved the issue,
however i now have a bigger problem

Given that i live in the UK the date format is in correct
Access always uses the American format #mm/dd/yy#
while in the UK our date is set to #dd/mm/yy#
this is giving me a problem. the records are stored in UK format and
the query is searching in US Format. Is there anyway i can force
access to search my date field in Uk Format and return the correct
records?

thanks
kevin

[Tom van Stiphout]

On 7 Jul 2004 05:10:44 -0700, kc******@ford.com (Kevin Carter) wrote:

Dates should be stored in fields with the date/time format, which is
some 8-byte format we don't need to know about.
For presentation reasons, you may need to apply a format, for example:
Format$(MyDateField, "mm/dd/yyyy")

-Tom.

Tom van Stiphout <no*************@cox.net> wrote in message news:<2n********************************@4ax.com>. ..

On 6 Jul 2004 10:44:29 -0700, ke***@carter5711.freeserve.co.uk (kevin
carter) wrote:

To debug, I would set a breakpoint after strSQL is concatenated, and
paste the resulting string in a new query. Try to run that query, and
the parser may point out some problems.

One likely problem is that you didn't wrap the date fields with
#-signs.

To stop errors: On Error Resume Next :-)

-Tom.

>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

thanks both for your reply , your suggestions resolved the issue,
however i now have a bigger problem

Given that i live in the UK the date format is in correct
Access always uses the American format #mm/dd/yy#
while in the UK our date is set to #dd/mm/yy#
this is giving me a problem. the records are stored in UK format and
the query is searching in US Format. Is there anyway i can force
access to search my date field in Uk Format and return the correct
records?

thanks
kevin

[David W. Fenton]

Tom van Stiphout <no*************@cox.net> wrote in
news:j7********************************@4ax.com:

Dates should be stored in fields with the date/time format, which
is some 8-byte format we don't need to know about.
For presentation reasons, you may need to apply a format, for
example: Format$(MyDateField, "mm/dd/yyyy")

Or use DateSerial().

Or format in an unambiguous format.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc