Working with MS-Access and writing SQL queries to run via QODBC to get data from QuickBooks, I ran in a problem with dates.

I needed to sum the sub-total of all the rows for a given year. So that is pretty simple SQL1:

SELECT SUM(subtotal) FROM Invoice WHERE YEAR(TimeCreated) = YEAR(NOW())

That does not work. So I tried to generate the year in the macro code and sticked that to the statement:

SELECT SUM(subtotal) FROM Invoice WHERE YEAR(TimeCreated) = 2009

And that does not work either.

I tried with >= just to make sure, in case it wasn't work properly. That worked! That is, YEAR(TimeCreated) is greater or equal to 2009. Good! Let's try 2010. Still larger or equal. Let's try 3000. Hmmmm! Still larger or equal! Let's try <= NOW(). That works. Let's try the Unix value representing Jan 1, 2008 at midnight. Sure enough, the value is represented in Unix time! So, let's convert the exact date to Unix time and try again. That fails. We may need to use UTC instead of the local time...

In any event, I find that strange so I tried to query the year as in:

SELECT YEAR(TimeCreated) FROM Invoice WHERE CustomerRefListID = '***'

Surprise! The result is the correct year (2009 for this one). And that's an integer, as expected. So big problem in there!

Just in case, I search with Google and cannot find much about this problem. I look at ways to write dates in MS-Access, and they mention the # character. You are supposed to write #<date>#. I try that and get an error saying that the query is all broken.

Okay... I read more docs and I find the Jet syntax. Thinking that may help, I try and get the "Malformed GUID in query expression" error. So Jet syntax is not supported (even though looking into the docs it feels very much like they give examples in MS-Access!)

Also, usually, in SQL you write dates between single quotes. So I tried the following too (that was my first attempt, in fact):

SELECT SUM(Subtotal) FROM Invoice WHERE TimeCreated
   BETWEEN '2009/01/01 00:00:00' AND '2009/12/31 00:00:00'

That looks neat and the query goes off! So it runs, but then reaches a point where the comparison needs to be checked. At that point it breaks with an error: "data type mismatch in criteria expression".

In other words, it could not recognize the date in the string as such. I tried to cast, but that failed completely (casts are not available).

I then contacted the QODBC people. They said they had no bug in there. And maybe that's the ODBC stuff from MS-Windows that is funky, dunno. In any event, even that failed:

SELECT SUM(Subtotal) FROM Invoice WHERE YEAR(TimeCreated) = YEAR(NOW())

The TimeCreated and NOW dates are both in 2009. But the result of that comparison is FALSE.

The first thing I tried was to test with the MS-Access dates. For that I wrote: #2009/01/01 00:00:00# and I get an error. The error reads: "ODBC--call failed".

Interestingly enough, if you put the time to all balls, it fails! I added one second and it works. The chances that someone at that company enters an invoice at midnight on new years eve are so close to zero that I'm pretty sure we're fine.

So finally I had it! The command goes like this:

SELECT SUM(SubTotal) FROM Invoice WHERE CustomerRefListID = 'customer ID'
       AND TimeCreated BETWEEN #2009/01/01 00:00:01# AND #2009/12/31 23:59:59#

That worked. Use 0 instead of 1 second in the first date and it fails!

Okay. So far, all the queries I have sent with the simple Execute order from the CurrentDB. This works great for simple queries. Now, in order to use a full query without the need to do weird fixes because of MS-Access, one should use the pass-through queries. This gives you full access to the layer handling the SQL order instead of having MS-Access "do stuff" to your query even before you have a chance to do anything yourself.

You had two such samples on the MS-Windows website. There is a copy of these examples. Important Note: they will work only if you have the Option Explicit turned on.

Function ParamSPT (NewGroup As String)

   Dim MyDb As DAO.Database, MyQ As QueryDef
   Set MyDb = CurrentDB()

   ' Create a temporary QueryDef object that is not saved.
   Set MyQ = MyDb.CreateQueryDef("")

   ' Type a connect string using the appropriate values for your
   ' server.
   MyQ.connect = "ODBC;DSN=DSNName;UID=UserName;PWD=Password;DATABASE=DatabaseName"

   ' Set ReturnsRecords to false in order to use the Execute method.
   MyQ.returnsrecords = False

   ' Set the SQL property and concatenate the variables.
   MyQ.sql = "sp_addgroup" & " " & NewGroup

   Debug.Print MyQ.sql
   MyQ.Execute
   MyQ.Close
   MyDb.Close

End Function

Function ParamSPT2(MyParam As String)

   Dim MyDb As DAO.Database, MyQry As QueryDef, MyRS As DAO.Recordset
   Set MyDb = CurrentDB()
   Set MyQry = MyDb.CreateQueryDef("")

   ' Type a connect string using the appropriate values for your
   ' server.
   MyQry.connect = "ODBC;DSN=DSNName;UID=UserName;PWD=Password;DATABASE=DatabaseName"

   ' Set the SQL property and concatenate the variables.
   MyQry.SQL = "sp_server_info " & MyParam

   MyQry.ReturnsRecords = True
   Set MyRS = MyQry.OpenRecordset()
   MyRS.MoveFirst

   Debug.Print MyRS!attribute_id, MyRS!attribute_name, _
      MyRS!attribute_value

   MyQry.Close
   MyRS.Close
   MyDb.Close

End Function

• 1. Although at first I tried with BETWEEN, I simplify my samples here