Count in Access VBA

Question

I am trying to insert count stats into a table as one record by using the following code in VBA. But for some reason, when I go to execute it it says the Number of query values and destination fields are not the same and I am confused as to why this is happening. 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 "


db.Execute strCreate
db.Execute strCount2

Is there truly an apostrophe in front of your definition of strCount2? — KevenDenen
– KevenDenen, Commented Jan 5, 2015 at 18:32

KevenDenen · Accepted Answer · 2015-01-05 18:42:26Z

1

It looks like you are missing a space in your query:

"SELECT A.cnt, B.cnt, C.cnt, D.cnt, E.cnt, F.cnt" & _
"FROM ( " & _

should be either

"SELECT A.cnt, B.cnt, C.cnt, D.cnt, E.cnt, F.cnt " & _
"FROM ( " & _

or

"SELECT A.cnt, B.cnt, C.cnt, D.cnt, E.cnt, F.cnt" & _
" FROM ( " & _

I prefer the first one with the space at the end of the first line as it maintains the line-up of the select and from.

answered Jan 5, 2015 at 18:42

KevenDenen

1,72213 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Pablo Over a year ago

Thanks. I fixed that but now it is saying that there are too few parameters and that it expected one.

Pablo Over a year ago

It says it is in the from clause.

KevenDenen Over a year ago

Which one? You have a bunch of from clauses in there.

Pablo Over a year ago

It doesn't specify. Would there be a way for me to find out?

KevenDenen Over a year ago

Can you run this query against your datasource directly without involving VBA and have it function correctly? 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.

Collectives™ on Stack Overflow

Count in Access VBA

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related