Results 1 to 2 of 2

Thread: Syntax Error

  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Syntax Error

    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •