Hi, Maybe I am missing something this morning, I keep pulling my hair out to try find where the problem lies with this code...

I keep getting a "Syntax Error (Missing Operator) in Query Expression" message when I try run this code. Where is it I am going wrong?

Jez


Sub GetData()
On Error GoTo Err:
strDWFilePath = "\\titan\data\informat\2006-2007\Returns\Waiting List\18 weeks\18WeekAnalysis.mdb"
Set cnnDW = New ADODB.Connection
Set rsDW = New ADODB.Recordset
Sheet4.Range("B8:F5000").ClearContents
cnnDW.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strDWFilePath & ";"
sQRY = "SELECT tblSummary1a.[Treatment function], Nz([Total],0)-Nz([Patients with unknown clock start date],0) AS [Number], " & _
"Nz([tblSummary1a]![>0-1],0)+Nz([tblSummary1a]![>1-2],0)+Nz([tblSummary1a]![>2-3],0)+Nz([tblSummary1a]![>3-4],0)+ " & _
"Nz([tblSummary1a]![>4-5],0)+Nz([tblSummary1a]![>5-6],0)+Nz([tblSummary1a]![>6-7],0)+Nz([tblSummary1a]![>7-8],0)+ " & _
"Nz([tblSummary1a]![>8-9],0)+Nz([tblSummary1a]![>9-10],0)+Nz([tblSummary1a]![>10-11],0)+Nz([tblSummary1a]![>11-12],0)+ " & _
"Nz([tblSummary1a]![>12-13],0)+Nz([tblSummary1a]![>13-14],0)+Nz([tblSummary1a]![>14-15],0)+Nz([tblSummary1a]![>15-16],0)+ " & _
"Nz([tblSummary1a]![>16-17],0)+Nz([tblSummary1a]![>17-18] AS [Seen <18 Weeks], Nz([tblSummary1a]![>36-37],0)+ " & _
"Nz([tblSummary1a]![>37-38],0)+Nz([tblSummary1a]![>38-39],0)+Nz([tblSummary1a]![>39-40],0)+Nz([tblSummary1a]![>40-41],0)+ " & _
"Nz([tblSummary1a]![>41-42],0)+Nz([tblSummary1a]![>42-43],0)+Nz([tblSummary1a]![>43-44],0)+ " & _
"Nz([tblSummary1a]![>44-45],0)+Nz([tblSummary1a]![>45-46],0)+Nz([tblSummary1a]![>46-47],0)+Nz([tblSummary1a]![>47-48],0)+ " & _
"Nz([tblSummary1a]![>48-49],0)+Nz([tblSummary1a]![>49-50],0)+Nz([tblSummary1a]![>50-51],0)+Nz([tblSummary1a]![>51-52],0)+ " & _
"Nz([tblSummary1a]![52 plus],0) AS [Over 36 Weeks], " & _
"tblSummary1a.[Reporting Month] " & _
"FROM tblSummary1a " & _
"ORDER BY tblSummary1a.[Reporting Month]"
rsDW.CursorLocation = adUseClient
rsDW.Open sQRY, cnnDW, adOpenStatic, adLockReadOnly
Application.ScreenUpdating = False
Sheet4.Range("B8").CopyFromRecordset rsDW
rsDW.Close
Set rsDW = Nothing
cnnDW.Close
Set cnnDW = Nothing
Exit Sub
Err:
MsgBox "The following error has occured-" & vbCrLf & vbCrLf & VBA.Error, vbCritical, "18 Week Analysis"
MsgBox VBA.Err
End Sub

Have you tried good old fashioned debugging? Put break points throughout your code and then step through to find the line causing problems?

I think it might even be the name of your error handler - change it so you're not using a reserved word.

*shrugs* HTH