question

minerman avatar image
minerman asked

Problem using 00:00:00 in datetime for MSAccess SQL Query

Hello, I have written an MSAccess query and it works perfect, except when I have 00:00:00 in the time part of a datetime query. Here is my query: SELECT FURNACED.TAG, CStr(FURNACED.DATETIME), FURNACED.VALUE FROM FURNACED FURNACED WHERE ((FURNACED.TAG='AMMONIA-TANK-TEMP') OR (FURNACED.TAG='LASTSTRING')) AND ((FURNACED.DATETIME>=**#02/21/2013 00:00:00#** And FURNACED.DATETIME<=**#02/22/2013 00:00:00#**)) AND (Datepart("n",FURNACED.DATETIME) = 00 Or Datepart("n",FURNACED.DATETIME) = 5 Or Datepart("n",FURNACED.DATETIME) = 10 Or Datepart("n",FURNACED.DATETIME) = 15 Or Datepart("n",FURNACED.DATETIME) = 20 Or Datepart("n",FURNACED.DATETIME) = 25 Or Datepart("n",FURNACED.DATETIME) = 30 Or Datepart("n",FURNACED.DATETIME) = 35 Or Datepart("n",FURNACED.DATETIME) = 40 Or Datepart("n",FURNACED.DATETIME) = 45 Or Datepart("n",FURNACED.DATETIME) = 50 Or Datepart("n",FURNACED.DATETIME) = 55) ORDER BY FURNACED.TAG, FURNACED.DATETIME, FURNACED.VALUE; If I change any 0 in the bolded times above, the query works fine. If not, then I get the error: ODBC--call failed.
odbc
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Dave_Green avatar image
Dave_Green answered
Because [Access stored dates as a double-precision, floating-point number][1] (not actually as a date), the value #02/21/2013 00:00:00# is equivalent to #02/21/2013#. I have tried your query in Access (2003) with a small set of data I made up and I note that omitting the time portion seems to produce the result you wanted, assuming I've interpreted your requirement correctly. Therefore, you simply need to remove the time portion when you want to compare datetime values which include the time at midnight. I do note though that you appear to be including both midnights (on the 21st and 22nd) in your query - is that correct? I ask as it looks like it could potentially lead to double counting when comparing two sample periods. [1]: http://support.microsoft.com/kb/210276
13 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

minerman avatar image minerman commented ·
I tried entering the dates without the time portion and I still get the same error. "ODBC--call failed" Yes, I included both mid-nights but that is not important. I actually have a vba script that is writing my code for me and this is only a sample of what my user may enter.
0 Likes 0 ·
Dave_Green avatar image Dave_Green ♦ commented ·
What is the exact sequence - VBA (within ACCESS?) composes query, which is executed against tables in access - or are these linked tables? I'm trying to understand where the ODBC connection is - an external table, or an external call to a table in Access?
0 Likes 0 ·
minerman avatar image minerman commented ·
I'm actually using VBA within' a non office application to write a .odc file. I have no concerns with the vba itself, it works with no issue. I am a little unsure of exactly how the connection works. (I'm not a database guy and I had a little help on creating this)... but there is a line in the .odc code that says the connection type is OLEDB. I'm not sure if that helps or not.
0 Likes 0 ·
minerman avatar image minerman commented ·
Ohh...I just reread your comment. Yes, the table is external to access. We just used access as a tool to query the table.
0 Likes 0 ·
Dave_Green avatar image Dave_Green ♦ commented ·
Ok, so to confirm my understanding - does the query work if you run it within access query (i.e. not via VBA)? Also, can you advise where the data table is (e.g. is it in a SQL server, etc)?
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.