Incorrect syntax near the keyword

I keep running into an error when running this section of the vb6 code.  

Incorrect Syntax near the keyword 'With'   I don't see the error so I need some help.  

Dim rec1 As ADODB.Recordset
Dim ApExcel As Object 'To open Excel
Dim Sheet As String
Dim num(1 To 40) As Double
    Dim LastName As String
    Dim FirstName As String
Dim First(1 To 18) As String
Dim Second(1 To 18) As String
Dim Third(1 To 18) As Integer
Dim ServerTime As Date
Dim MinimumDate As Date
Dim MaximumDate As Date

Private Sub Command1_Click()

'On Error GoTo CheckError1

Set ApExcel = CreateObject("Excel.application") 'Creates an object
ApExcel.Visible = True ' So you can see Excel

Set conn = New ADODB.Connection
Set rec = New ADODB.Recordset
Set rec1 = New ADODB.Recordset
conn.Open "Provider=sqloledb;Data Source=" & ConnectionIP & ",1433;Network Library=DBMSSOCN;Initial Catalog= " & CAPDB & "; User ID=xx;Password=xxxx"


esql = "With CTE_Hours as (select distinct AgencyID, Agency, ActivityID, RegID, cast(ActivityDate as Date) ActivityDate, Fiscal From tblOrgHours Where [Hours] > 0) select H.Agency,A.ActivityName,H.ActivityDate,Count(H.RegID) as [# individuals]" & _
       ",SUM(CASE when R.AgeCurrent >= 11 and R.AgeCurrent <= 13 then 1 else 0 end) as [Ages 11-13]" & _
       ",SUM(CASE when R.AgeCurrent >= 14 and R.AgeCurrent <= 18 then 1 else 0 end) as [Ages 14-18]" & _
       ",SUM(CASE when R.AgeCurrent >= 19 and R.AgeCurrent <= 24 then 1 else 0 end) as [Ages 19-24]" & _
       ",SUM(CASE when R.AgeCurrent >= 25 and R.AgeCurrent <= 65 then 1 else 0 end) as [Ages 25-65]" & _
       ",SUM(CASE when R.AgeCurrent >= 66 then 1 else 0 end) as [Ages 65+]" & _
       ",SUM(CASE when R.Race = 'Asian' then 1 else 0 end) as [Asian]" & _
       ",SUM(CASE when R.Race = 'African-American' then 1 else 0 end) as [African-American]" & _
       ",SUM(CASE when R.Race = 'Caucasian' then 1 else 0 end) as [Caucasian]" & _
       ",SUM(CASE when R.Race = 'Native-American' then 1 else 0 end) as [Native-American]" & _
       ",SUM(CASE when R.Race = 'Multi-Racial' then 1 else 0 end) as [Multi-Racial]" & _
       ",SUM(CASE when R.Race = 'Latino-Hispanic' then 1 else 0 end) as [Latino-Hispanic]" & _
       ",SUM(CASE when R.Gender = 'Male' then 1 else 0 end) as [Male]" & _
       ",SUM(CASE when R.Gender = 'Female' then 1 else 0 end) as [Female] from CTE_Hours H inner join tblOrgRegistrations R on H.Regid = R.RegID inner join tblOrgActivities A on H.ActivityID = A.ActivityID where R.AgeCurrent between 11 and 999 And h.Agency = 'Cicero Area Project' And H.Fiscal = 2018 And H.ActivityDate >= '" & DTPicker1 & "' And H.ActivityDate < '" & DTPicker2 & "' group by H.Agency,A.ActivityName,H.ActivityDate Order by 1,2,3"

If rec.State = adStateOpen Then
End If

      rec.CursorType = adOpenStatic
      rec.CursorLocation = adUseClient
      rec.LockType = adLockOptimistic
      rec.Open esql, conn, , , adCmdText

Text1 = rec.RecordCount

If rec.EOF And rec.BOF Then
    Set DataGrid1.DataSource = Nothing
    MsgBox "There are no Records for this Query. ", vbOKOnly, "No Data Found"
    Exit Sub
End If

        Set DataGrid1.DataSource = rec

End Sub

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
I don't see any syntax problems in that code, can you take a screen capture of the error?  Is this part of a larger body of code, could the error be somewhere else?

al4629740Author Commented:
I'm positive it only lies here because nothing else is referenced to get that error.
Chris WatsonSoftware DeveloperCommented:
What database server are you using? Does it support Common Table Expressions?
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Chris WatsonSoftware DeveloperCommented:
Also, try prefacing your statement with a semicolon.

esql = ";With CTE_Hours..."

Open in new window


Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
al4629740Author Commented:
I’m using 2012 sql
Chris WatsonSoftware DeveloperCommented:
OK, CTEs should be supported in that version. Did adding the semicolon make any difference?
al4629740Author Commented:
it worked!
Chris WatsonSoftware DeveloperCommented:
Ha! Apparently, as a CTE is required to be separated from any previous statement in the batch, if any other statements are added to your query before it is executed (I found examples suggesting things such as "SET NOCOUNT ON") you get this error. The addition of the semicolon ensures that the previous statement, if any, is terminated with a semicolon.

al4629740Author Commented:
Very interesting
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.