Count SQL statement in Access VBA

Question

I am trying to count records in an access table where certain criteria are met and insert the totals into another table in one record. I am doing so through vba and am trying to create a sql statement in there but for some reason it says I have too many line continuations when I try to add in anymore and I am really confused as to why. Any help would be greatly appreciated.

intYear = InputBox("What year is it currently?", "Year Input")

DoCmd.DeleteObject acTable, "ThisTable"



strCreate = "CREATE TABLE MarketSegmentTotals (" & vbCrLf & _
            "[State Medicaid] TEXT," & vbCrLf & _
            "Commercial TEXT," & vbCrLf & _
            "HIX TEXT," & vbCrLf & _
            "MMP TEXT," & vbCrLf & _
            "[CMS Part D (CY " & intYear & ")] TEXT," & vbCrLf & _
            "[CMS Part D (CY " & (intYear + 1) & ")] TEXT" & vbCrLf & _
            ");"


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 " & _
"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 "

KevenDenen · Accepted Answer · 2015-01-05 17:38:50Z

3

VBA limits the number of lines joined by line continuation characters.

You can change this to redefine the variable to break the number of line continuation.

strCount = "blahblahblah" & _
  "moreblahblahblah" & _
  "lastblahforabit" 
strCount = strCount & "evenmoreblah" & _
  "toomuchblahblahblah"

Or eliminate the line continuation entirely.

strCount = "blahblahblah"
strCount = strCount & "moreblahblahblah"
strCount = strCount & "lastblahforabit" 
strCount = strCount & "evenmoreblah"
strCount = strCount & "toomuchblahblahblah"

edited Jan 5, 2015 at 17:38

answered Jan 5, 2015 at 17:29

KevenDenen

1,72213 silver badges26 bronze badges

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

2 Comments

RubberDuck Over a year ago

Interesting factiod. This behavior isn't exactly in the language spec. An implementation MAY limit the number of characters in an <extended-line>.

KevenDenen Over a year ago

It isn't very well documented, but there are some references to it in a few different places. One Two. It doesn't seem to be very consistent, as the first link suggest the limit is 25, but the question's code isn't over that limit, yet still errors.

Collectives™ on Stack Overflow

Count SQL statement in Access VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related