Incorrect syntax?

**supportservice** · 08-25-2015, 12:29 PM

Hello,
There's this Module in Excel file (.xlsm)

For some reason it is erroring.

Run-time error 1004
Incorrect syntax near 'pm'

Sub Button1_Click()

Dim StartingBatchDate As String, EndingBatchDate As String, Split1 As String, Split2 As String, Split3 As String, Split4 As String, AdditionalMemberNumbers As String

 StartingBatchDate = Range("B2")
 EndingBatchDate = Range("B3")
 
 With ActiveWorkbook.Connections("RegularMemberships").OLEDBConnection
    Split1 = "SELECT DISTINCT person.MembershipNumber, " & _
     "person.FirstName + ' ' + person.LastName as NAME, person.FirstName, person.RegionId as REGION" & _
     "addr.StreetOne, addr.StreetTwo, ISNULL(unit.description, '') + ' ' + addr.Subunit 'Unit', addr.City + ', ' + addrState.Code + '  ' + addr.PostalCode 'CityStateZip', addrState.Code 'State', Country.Description 'Country', " & _
     "pm.HomeClub, CONVERT( char(10), pm.EndDate, 101 ) EndDate, addr.PostalCode "
    Split2 = "FROM ( SELECT PersonId, ISNULL( OrganizationName, 'Individual' ) HomeClub, MembershipTypeId, InvoiceNumber, EndDate " & _
                    "FROM attribute.PersonMembership pm " & _
                    "LEFT JOIN USFSA.dbo.SOP10100 invWork ON InvoiceNumber = invWork.SOPNUMBE " & _
                    "LEFT JOIN USFSA.dbo.SOP30200 invHist ON InvoiceNumber = invHist.SOPNUMBE " & _
                    "JOIN lookup.MemberTypes mt ON mt.Id = PersonMembership.MembershipTypeId AND MemberGroup = 'Regular Member' " & _
                    "LEFT JOIN entity.Organization org ON org.Id = PersonMembership.OrganizationId " & _
                    "WHERE "
        Split3 = " PersonMembership.CreatedDate > DATEADD( day, -120, GETDATE() ) AND " & _
                 " ( ( SUBSTRING( invWork.BACHNUMB, 1, 8 ) >= '" + StartingBatchDate + "' AND SUBSTRING( invWork.BACHNUMB, 1, 8 ) <= '" + EndingBatchDate + "' ) " & _
                 "OR ( SUBSTRING( invHist.BACHNUMB, 1, 8 ) >= '" + StartingBatchDate + "' AND SUBSTRING( invHist.BACHNUMB, 1, 8 ) <= '" + EndingBatchDate + "' ) ) ) pm "
    Split4 = "JOIN entity.Person person ON person.Id = pm.PersonId " & _
        "LEFT JOIN lookup.TitlePrefix ON person.PrefixId = TitlePrefix.Id AND TitlePrefix.Id > 0 " & _
        "LEFT JOIN lookup.TitleSuffix ON person.SuffixId = TitleSuffix.Id AND TitleSuffix.Id > 0 " & _
        "JOIN attribute.Address addr ON person.PrimaryAddressId = addr.Id " & _
        "LEFT JOIN lookup.AddressSubunit unit ON unit.id = Addr.SubunitTypeId AND addr.SubunitTypeId <> 0 " & _
        "LEFT JOIN lookup.State addrState ON addr.StateId = addrState.Id " & _
        "LEFT JOIN lookup.Country ON addr.CountryId = Country.Id AND addr.CountryId > 0 AND addr.CountryId <> 42 " & _
        "ORDER BY addr.PostalCode"
    .CommandText = Split1 + Split2 + Split3 + Split4

  End With
  
  ActiveWorkbook.Connections("RegularMemberships").Refresh

End Sub

I cannot figure out what it is. When I click on Debug it takes me to the Module and to this line

  ActiveWorkbook.Connections("RegularMemberships").Refresh

In the Properties under Connection, the SQL works. Verified it on SQL Server Management Studio 2014.

Help would be appreciated.

Here's the SQL that works as is in the Connection Properties for the Excel Macro:

SELECT DISTINCT 
                         person.MembershipNumber, person.FirstName + ' ' + person.LastName AS NAME, person.FirstName, person.RegionId AS REGION, 
                         addr.StreetOne, addr.StreetTwo, ISNULL(unit.Description, '') + ' ' + addr.SubUnit AS 'Unit', addr.City + ', ' + addrState.Code + '  ' + addr.PostalCode AS 'CityStateZip', 
                         addrState.Code AS 'State', lookup.Country.Description AS 'Country', CONVERT(char(10), pm.EndDate, 101) AS EndDate, addr.PostalCode, pm.HomeClub
FROM            (SELECT        attribute.PersonMembership.PersonId, ISNULL(org.OrganizationName, N'Individual') AS HomeClub, attribute.PersonMembership.MembershipTypeId, 
                                                    attribute.PersonMembership.InvoiceNumber, attribute.PersonMembership.EndDate
                          FROM            attribute.PersonMembership LEFT OUTER JOIN
                                                    USFSA.dbo.SOP10100 AS invWork ON attribute.PersonMembership.InvoiceNumber = invWork.SOPNUMBE LEFT OUTER JOIN
                                                    USFSA.dbo.SOP30200 AS invHist ON attribute.PersonMembership.InvoiceNumber = invHist.SOPNUMBE INNER JOIN
                                                    lookup.MemberTypes AS mt ON mt.Id = attribute.PersonMembership.MembershipTypeId AND mt.MemberGroup = 'Regular Member' LEFT OUTER JOIN
                                                    entity.Organization AS org ON org.Id = attribute.PersonMembership.OrganizationId
                          WHERE        (attribute.PersonMembership.CreatedDate > DATEADD(day, - 120, GETDATE())) AND (SUBSTRING(invWork.BACHNUMB, 1, 8) >= '20150601' AND 
                                                    SUBSTRING(invWork.BACHNUMB, 1, 8) <= '20150710' OR
                                                    SUBSTRING(invHist.BACHNUMB, 1, 8) >= '20150601' AND SUBSTRING(invHist.BACHNUMB, 1, 8) <= '20150710')) AS pm INNER JOIN
                         entity.Person AS person ON person.Id = pm.PersonId LEFT OUTER JOIN
                         lookup.TitlePrefix ON person.PrefixId = lookup.TitlePrefix.Id AND lookup.TitlePrefix.Id > 0 LEFT OUTER JOIN
                         lookup.TitleSuffix ON person.SuffixId = lookup.TitleSuffix.Id AND lookup.TitleSuffix.Id > 0 INNER JOIN
                         attribute.Address AS addr ON person.PrimaryAddressId = addr.Id LEFT OUTER JOIN
                         lookup.AddressSubUnit AS unit ON unit.Id = addr.SubUnitTypeId AND addr.SubUnitTypeId <> 0 LEFT OUTER JOIN
                         lookup.State AS addrState ON addr.StateId = addrState.Id LEFT OUTER JOIN
                         lookup.Country ON addr.CountryId = lookup.Country.Id AND addr.CountryId > 0 AND addr.CountryId <> 42
ORDER BY addr.PostalCode