Run-time error 3061 - Too few parameters. Expected 1

[ria.arora]

Dear All,

I'm having wiered issue with one of my SQL.

Below SQL I'm executing from VBA, which is throwing error "Run-time error 3061 - Too few parameters. Expected 1"

SELECT tbl_Bankers_Mapping.[GMIS Revenue Producer], SUM([Qtr Annuity Income]) - SUM(NZ([Qtr Annuity Oneoff],0))/3 AS [QtrAnnuity] FROM tbl_Consol_Monthly_Revenue_Split AS CMR INNER JOIN tbl_Bankers_Mapping ON CMR.[Private Banker] = tbl_Bankers_Mapping.[GMIS Revenue Producer] WHERE [YearMonth] = '201209' AND [Region 2]= 'South Asia' AND tbl_Bankers_Mapping.[Exclude] IN (False) AND CMR.[Private Banker] LIKE "*"  GROUP BY tbl_Bankers_Mapping.[GMIS Revenue Producer] ,[Region 2] ORDER BY [Region 2], tbl_Bankers_Mapping.[GMIS Revenue Producer]

If I run same SQL in Backend Database (Access 2007) then it runs without any error.

If I takeout - SUM(NZ([Qtr Annuity Oneoff],0)) from above query then it works fine. Can someone tell me what's wrong I'm doing over here.

Regards

 

[pr2-eugin]

If you put this query inside a Normal Access Query window are you able to get the desired result? Try a Debug.Print before you execute the Query that will show how it is actually understood by the DB.. So you will be able to see something there..

 

[ria.arora]

Hi Paul

Yes I'm able to run the query from Normal Access Query window and got correct result as well.

 

[pr2-eugin]

Try switching the Views as the problem might occur when you are missing parentheses.. Also what method are you using to execute the SQL??

 

[ria.arora]

using below syntex

Set rsTmp = gsPnPDatabase.OpenRecordset(strSelectSQL)

 

[pyrodie18]

Can you post a copy of the statement that sets strSelectSQL

 

[ria.arora]

That's the one which I posted in my first post

SELECT tbl_Bankers_Mapping.[GMIS Revenue Producer], SUM([Qtr Annuity Income]) - SUM(NZ([Qtr Annuity Oneoff],0))/3 AS [QtrAnnuity] FROM tbl_Consol_Monthly_Revenue_Split AS CMR INNER JOIN tbl_Bankers_Mapping ON CMR.[Private Banker] = tbl_Bankers_Mapping.[GMIS Revenue Producer] WHERE [YearMonth] = '201209' AND [Region 2]= 'South Asia' AND tbl_Bankers_Mapping.[Exclude] IN (False) AND CMR.[Private Banker] LIKE "*"  GROUP BY tbl_Bankers_Mapping.[GMIS Revenue Producer] ,[Region 2] ORDER BY [Region 2], tbl_Bankers_Mapping.[GMIS Revenue Producer]