MS Access dateTime query

Lakshmi

Delphi Developer

Sun, 21 Nov 2004 22:17:27 GMT

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

Brian Bushay Team

Delphi Developer

Mon, 22 Nov 2004 11:25:26 GMT

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

Menno Avegaar

Delphi Developer

Tue, 23 Nov 2004 05:46:27 GMT

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

1. DateTime-Query in MS-Access

2. Delphi 4 + Native Ms Access 97 driver + MS Access queries

3. DateTime-Fields with Access and MS-SQL

4. INSERT INTO Problem with DateTime (MS Access)

5. UPDATE statement (MS-ACCESS) w/DateTime field

6. Problem querying Access DATETIME fields

7. Can I access to MS Access queries???

8. Query in Access with native MS Access (D3)

9. Accessing MS Access query

10. D5-ADO-MS ACCESS 97 database-MS Office 97 Access ODBC driver-Autoincrement probs