Board index » delphi » MS Access dateTime query

MS Access dateTime query

Hi,

I am using MS Access 97 and Delphi 5 with ODBC.  I am more familiar with
Oracle and SQL server.  I am a bit uncertain about Access.  I need to query
the Access database based on datetime.  Now, I thought using # should do the
trick.

TIME_TAG is of type DateTime in the table XC_Data1

This is the query I am using:

Select ORIG_VALUE FROM XC_DATA1 WHERE TIME_TAG = #01/03/2000 02:45:00#

AND SITSEN_SENSORNAME = 'CPR' AND SITSEN_STE_SITE_ID = 'BUNK'

But I get an error "45:00#" is an invalid field".  However when I query
without the time part i.e, #01/03/2000#, it works.  The same query (above)
gives me a result in SQL explorer.  Is there something I am missing here?

Thanks,

Lakshmi

 

Re:MS Access dateTime query


Quote
> Now, I thought using # should do the
>trick.

Not for ODBC

Quote
>TIME_TAG is of type DateTime in the table XC_Data1
>This is the query I am using:
>Select ORIG_VALUE FROM XC_DATA1 WHERE TIME_TAG = #01/03/2000 02:45:00#
>AND SITSEN_SENSORNAME = 'CPR' AND SITSEN_STE_SITE_ID = 'BUNK'

>But I get an error "45:00#" is an invalid field".  However when I query
>without the time part i.e, #01/03/2000#, it works.  The same query (above)
>gives me a result in SQL explorer.  Is there something I am missing here?

Try formatting time like this with period instead of colon as a separator.
Delphi is getting tripped up interpreting the colon's as parameters

 TIME_TAG = '01/03/2000 02.45.00'
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Re:MS Access dateTime query


The BDE thinks the time separator is the start of a parameter definition. To
stop the BDE from checking the query for parameters set the ParamCheck
property to False.

"Lakshmi" <lvasude...@sutron.com> schreef in bericht
news:3cfe1c25_2@dnews...

Quote
> Hi,

> I am using MS Access 97 and Delphi 5 with ODBC.  I am more familiar with
> Oracle and SQL server.  I am a bit uncertain about Access.  I need to
query
> the Access database based on datetime.  Now, I thought using # should do
the
> trick.

> TIME_TAG is of type DateTime in the table XC_Data1

> This is the query I am using:

> Select ORIG_VALUE FROM XC_DATA1 WHERE TIME_TAG = #01/03/2000 02:45:00#

> AND SITSEN_SENSORNAME = 'CPR' AND SITSEN_STE_SITE_ID = 'BUNK'

> But I get an error "45:00#" is an invalid field".  However when I query
> without the time part i.e, #01/03/2000#, it works.  The same query (above)
> gives me a result in SQL explorer.  Is there something I am missing here?

> Thanks,

> Lakshmi

Other Threads