Too few parameters in Access VBA

Question

I am trying to run this SQL statement in VBA and for some reason it says there are too few parameters and that it expected 1. I cannot figure out which line its on. Any help would be greatly appreciated.

strCount = "INSERT INTO MarketSegmentTotals([State Medicaid], [Commercial], [HIX], [MMP], [CMS Part D (CY " & intYear & ")], [CMS Part D (CY " & (intYear + 1) & ")] ) " & _
"SELECT A.cnt, B.cnt, C.cnt, D.cnt, E.cnt, F.cnt " & _
"FROM ( " & _
    "SELECT COUNT([FORMULARY ID]) as cnt " & _
    "FROM ImportMetricsIDs " & _
    "WHERE [Market Segment]= 'State Medicaid' " & _
") AS A " & _
", ( " & _
    "SELECT COUNT([FORMULARY ID]) as cnt " & _
    "FROM ImportMetricsIDs " & _
    "WHERE [Market Segment]= 'Commercial' " & _
") as B " & _
", ( " & _
    "SELECT COUNT([FORMULARY ID]) as cnt " & _
    "FROM ImportMetricsIDs " & _
    "WHERE [Market Segment]= 'HIX' " & _
") AS C " & _
", ( " & _
    "SELECT COUNT([FORMULARY ID]) as cnt " & _
    "FROM ImportMetricsIDs " & _
    "WHERE [Market Segment]= 'MMP' " & _
") AS D "

strCount2 = strCount & _
", ( " & _
    "SELECT COUNT([FORMULARY ID]) as cnt " & _
    "FROM ImportMetricsIDs " & _
    "WHERE [Market Segment]= 'CMS Part D (CY ' & (intYear) & ')'" & _
") AS E " & _
", ( " & _
    "SELECT COUNT([FORMULARY ID]) as cnt " & _
    "FROM ImportMetricsIDs " & _
    "WHERE [Market Segment]= 'CMS Part D (CY ' & (intYear + 1) & ')'" & _
") AS F "

I'm guessing (CY ' & (intYear) & ') was supposed to be (CY " & (intYear) & ") (and likewise for (intYear + 1)), but really you should probably be using a stored procedure instead. — p.s.w.g
– p.s.w.g, Commented Jan 5, 2015 at 19:30
Can you run this query against your datasource directly without involving VBA and have it function correctly? There seem to be some oddities in this query (like no joins on your subqueries). An easy way to do this would be to add a Debug.Print strCount2 in before your db.Execute line, then in your immediate window you can copy the resulting query and run it against your datasource. — KevenDenen
– KevenDenen, Commented Jan 5, 2015 at 19:32
The beauty of Keven's suggestion is that when you copy the statement text from the Immediate window, paste it into SQL View of a new query in the query designer, and attempt to run it, Access will display an input box which asks you to supply a value for the parameter. And that input box includes the name of the parameter. What is name of the parameter? — HansUp
– HansUp, Commented Jan 5, 2015 at 20:04

HansUp · Accepted Answer · 2015-01-05 20:41:44Z

I think you have a problem with quotes in your statement text. Look at this excerpt based on your code when I tested in the Immediate window:

intYear = 2015
? "WHERE [Market Segment]= 'CMS Part D (CY ' & (intYear) & ')'"
WHERE [Market Segment]= 'CMS Part D (CY ' & (intYear) & ')'

That can't be right. And when Access tries to execute the query and sees intYear, it interprets that to be a parameter because the db engine knows nothing about a VBA variable named intYear.

I think it should look like this:

? "WHERE [Market Segment]= 'CMS Part D (CY " & (intYear) & ")'"
WHERE [Market Segment]= 'CMS Part D (CY 2015)'

I encourage you to follow KevenDenen's advice to add Debug.Print strCount2 to the code after it has finished building the strCount2 string. Then you can run the code and view the text of the completed statement in the Immediate window. (You can use Ctrl+g to go to the Immediate window.) It helps tremendously to examine the actual statement your code is asking Access to execute.

Collectives™ on Stack Overflow

Too few parameters in Access VBA

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related