Tonight I spent 3 hours trying to get a simple, basic SQL query to work in Microsoft Access 2003. The SQL query runs perfectly in Microsoft SQL Server 2005, however when run through the VB code in Access via the DAO layer it kept on failing with a syntax error. Obviously the error message did not point out the cause of the error. Since the SQL was perfectly valid and not complex, I was baffled.

Here is the basic query I had written:

select sum([table one].[field1]) from [table one] inner join [table two] on [table one].[field2] = [table two].[field2] inner join [table three] on [table three].[field3] = [table two].[field3] where [table one].[field4] > '2005-01-01'

So... I started doubting the square bracket notation, the date quotes, the "inner join" syntax versus the where join alternative syntax etc. All to no avail.

The first thing I picked up was that dates need to be passed in with #2005-01-01# and not in single quotes like you do with SQL Server. Fine. But the query still failed. Eventually I came across this article and it solved my problem. It seems that in Access, you have to group multiple "inner join" clauses in brackets like so:

select sum([table one].[field1]) from ([table one] inner join [table two] on [table one].[field2] = [table two].[field2]) inner join [table three] on [table three].[field3] = [table two].[field3] where [table one].[field4] > #2005-01-01#

The () brackets surrounding the first inner join groups it - and this makes the difference between meeting your deadline or not.

I hate Access!