CODEWITHSUNDEEP
sqlms-accessvbams-access-2010
Pablo
Pablo

Reputation: 93

Count SQL statement in Access VBA

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 "

Upvotes: 1

Views: 208

Answers (1)

KevenDenen
KevenDenen

Reputation: 1726

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"

Upvotes: 3

Related Questions