VBA/ADO Runtime Error 1

[eAlchemist]

I keep getting a runtime error: The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect. The query works fine if I paste it in Access, but won't work through ADO. I can't, for the life of me, figure out what the problem is. Someone please help!!! Here is the SQL:

SELECT [qry_Cost].[Year], IIF(Sum([qry_RetailCommitment].[Feb])<>0,(Sum([qry_RetailCommitment].[Feb])- Sum([qry_Cost].[Feb]))/Sum([qry_RetailCommitment].[Feb]),0) AS Feb,IIF(Sum([qry_RetailCommitment].[Mar])<>0,(Sum([qry_RetailCommitment].[Mar])- Sum([qry_Cost].[Mar]))/Sum([qry_RetailCommitment].[Mar]),0) AS Mar,IIF(Sum([qry_RetailCommitment].[Apr])<>0,(Sum([qry_RetailCommitment].[Apr])- Sum([qry_Cost].[Apr]))/Sum([qry_RetailCommitment].[Apr]),0) AS Apr,IIF(Sum([qry_RetailCommitment].[May])<>0,(Sum([qry_RetailCommitment].[May])- Sum([qry_Cost].[May]))/Sum([qry_RetailCommitment].[May]),0) AS May,IIF(Sum([qry_RetailCommitment].[Jun])<>0,(Sum([qry_RetailCommitment].[Jun])- Sum([qry_Cost].[Jun]))/Sum([qry_RetailCommitment].[Jun]),0) AS Jun,IIF(Sum([qry_RetailCommitment].[Jul])<>0,(Sum([qry_RetailCommitment].[Jul])- Sum([qry_Cost].[Jul]))/Sum([qry_RetailCommitment].[Jul]),0) AS Jul,
IIF(Sum([qry_RetailCommitment].[Aug])<>0,(Sum([qry_RetailCommitment].[Aug])- Sum([qry_Cost].[Aug]))/Sum([qry_RetailCommitment].[Aug]),0) AS Aug,IIF(Sum([qry_RetailCommitment].[Sep])<>0,(Sum([qry_RetailCommitment].[Sep])- Sum([qry_Cost].[Sep]))/Sum([qry_RetailCommitment].[Sep]),0) AS Sep,IIF(Sum([qry_RetailCommitment].[Oct])<>0,(Sum([qry_RetailCommitment].[Oct])- Sum([qry_Cost].[Oct]))/Sum([qry_RetailCommitment].[Oct]),0) AS Oct,IIF(Sum([qry_RetailCommitment].[Nov])<>0,(Sum([qry_RetailCommitment].[Nov])- Sum([qry_Cost].[Nov]))/Sum([qry_RetailCommitment].[Nov]),0) AS Nov,IIF(Sum([qry_RetailCommitment].[Dec])<>0,(Sum([qry_RetailCommitment].[Dec])- Sum([qry_Cost].[Dec]))/Sum([qry_RetailCommitment].[Dec]),0) AS Dec,
IIF(Sum([qry_RetailCommitment].[Jan])<>0,(Sum([qry_RetailCommitment].[Jan])- Sum([qry_Cost].[Jan]))/Sum([qry_RetailCommitment].[Jan]),0) AS Jan
FROM [qry_RetailCommitment] INNER JOIN [qry_Cost] ON ([qry_RetailCommitment].[CategoryKey] = [qry_Cost].[CategoryKey]) AND ([qry_RetailCommitment].[Year] = [qry_Cost].[Year])
WHERE ((([qry_Cost].[DivNum])= 11) AND (([qry_Cost].[Year])="2006"))
GROUP BY [qry_Cost].[Year];

 

[PHV]

If qry_Cost.Year is defined as numeric:
WHERE qry_Cost.DivNum=11 AND qry_Cost.Year=2006
otherwise:
WHERE qry_Cost.DivNum=11 AND qry_Cost.Year='2006'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

Jan, Feb etc are Column Names.

Try using something else in your AS alias

Skip,
[sub]
[red][/red]
[/sub]

 

[eAlchemist]

I don't think it's the double quotes (and no, qry_Cost.Year is not numeric), as I'm using the same criteria elsewhere with no problem.
Could it be the aliases? That doesn't make sense to me, but I guess I'll try it. Why would this work in Access, but not in ADO?

Thanks,
Chris

 

[eAlchemist]

Sure enough. It was the aliases. Thanks Skip!!

 

[eAlchemist]

And thanks for all the help you've provided over the years PHV!!!