Works in query, but not in VBA

[Stapes]

SELECT TTEMP_CampaignRun04.PK_Contact,
QRY_ContactAnnualPurchases01_Crosstab.AcNo,
nz([QRY_ContactAnnualPurchases01_Crosstab].[2006/2007],0) AS [Total
Spend],
QRY_ContactAnnualPurchases01_Crosstab.[Total Of SumOfTotalSpend],
TM_CompContact.Archive,
TM_CompContact.OnHold,
TTEMP_CampaignRun04.Group,
TM_CompContact.CreationDate
INTO TTEMP_CampaignRun06
FROM TTEMP_CampaignRun04
INNER JOIN (TM_CompContact
INNER JOIN QRY_ContactAnnualPurchases01_Crosstab
ON TM_CompContact.PK_Contact =
QRY_ContactAnnualPurchases01_Crosstab.PK_Contact)
ON TTEMP_CampaignRun04.PK_Contact = TM_CompContact.PK_Contact
WHERE (((TM_CompContact.Archive)<>-1)
AND ((TM_CompContact.OnHold)<>-1)
AND ((TTEMP_CampaignRun04.Group)<>86)
AND ((nz([QRY_ContactAnnualPurchases01_Crosstab].[2006/2007],0))=0));

Hi

This code worked fine in the SQL of an Access Query, but in VBA code
it says there is a syntax error in the JOIN operation! I know it is
complex - which is why I tested it as a query first.
So where is the bloomin' syntax error?

Stapes

 

[Albert D. Kallal]

So, you saved the above query.

Try the following in code:

Dim rstData As DAO.Recordset
Dim strSql As String

strSql = CurrentDb.QueryDefs("TTEMP").SQL
strSql = Left(strSql, InStr(strSql, ";") - 1)

Debug.Print strSql

Set rstData = CurrentDb.OpenRecordset(strSql)

the above will display your raw sql in the debug.window.

If the above query runs from the query builder, but the above code don't
run, then at least you know the sql is the same

What I would do is take your code, and do a debug-print RIGHT BEFORE you
build the reocrdset. Then cut + paste that sql that displays in the
debug-window into a new blank query...does that query run?