Syntax error (missing operator) when setting query sql

**munkee** · Jul 22 '10, 10:30 AM

BINGO! I decided to print out all the sql and just sit and stare at it.. low and behold another question I have answered myself.

Basically.. I was missing an extra set of "".

The original sql had:

Code:

","[SumOfCostFig]>=" & [TotalCost] & "")/DSum

There is a double "" just before the )/DSum which in my vba I had as just ". Adding an extra few "'s solved it =]

**MikeTheBike** · Jul 22 '10, 11:31 AM

Originally posted by munkee

I am trying to dynamically update the rowsource of a chart within access.

To achieve this I moved the original rowsource sql of the chart which is:

Code:

SELECT tblParetoRaising.NCType, tblParetoRaising.SumOfCostFig AS TotalCost, DSum("[SumOfCostFig]","tblParetoRaising","[SumOfCostFig]>=" & [TotalCost] & "")/DSum("[SumOfCostFig]","tblParetoRaising") AS CPct FROM tblParetoRaising;

And replaced it with the rowsource being a query called "qryPareto"

I update the qryPareto using QueryDefs which should allow me to input different sql in to qryPareto and the chart will update accordingly.

However, When I try and insert the above sql I get the following error:

Syntax Error (missing operator)in query expression 'DSum("[SumOfCostFig]]","tblParetoRai sing","[SumOfCostFig]>=" & [TotalCost] &")/DSum("[SumOfCostFig]","tblParetoRai sing") AS CPct FROM tblParetoRaisin g;'.

I have checked the sql I have already doubled up on " where needed:

Code:

rowsource1 = "SELECT tblParetoRaising.NCType, tblParetoRaising.SumOfCostFig AS TotalCost, DSum(""[SumOfCostFig]"",""tblParetoRaising"",""[SumOfCostFig]>="" & [TotalCost] & "")/DSum(""[SumOfCostFig]"",""tblParetoRaising"") AS CPct FROM tblParetoRaising;"

But I still get the same error. Any ideas?

The code I use to update is below:

Code:

Dim dbs As Database
Dim ParetoQuery As QueryDef
Dim sqlstring As String
Dim sqlstring2 As String
Dim rowsource1 As String

Set dbs = CurrentDb
Set ParetoQuery = dbs.QueryDefs("qryPareto")

rowsource1 = "SELECT tblParetoRaising.NCType, tblParetoRaising.SumOfCostFig AS TotalCost, DSum(""[SumOfCostFig]"",""tblParetoRaising"",""[SumOfCostFig]>="" & [TotalCost] & "")/DSum(""[SumOfCostFig]"",""tblParetoRaising"") AS CPct FROM tblParetoRaising;"


ParetoQuery.SQL = rowsource1
dbs.QueryDefs.Refresh

Me.grpPareto1.Requery

Set ParetoQuery = Nothing
Set dbs = Nothing
Exit Sub

Hi

Appart from thr extra closing square bracket in your error message (which you seem to have corrected!), I think you have to man double quoted round the [TotalCost] (which I assume is a field in the form's recordset where the code is running !!??).

If that is the case, then the query string should be

Code:

rowsource1 = "SELECT tblParetoRaising.NCType, tblParetoRaising.SumOfCostFig AS TotalCost, DSum(""[SumOfCostFig]"",""tblParetoRaising"",""[SumOfCostFig]>=" & [TotalCost] & ")/DSum(""[SumOfCostFig]"",""tblParetoRaising"") AS CPct FROM tblParetoRaising;"

??

MTB

**munkee** · Jul 22 '10, 11:40 AM

Thank you for the reply Mike I will take a look to see if I can remove it. My knowledge of vba I would say is begginer/intermediate I seem to just bolt things together or create some small code to link other parts so it takes me a while to get my head around a lot of things.

It is quite a messy situation at the moment just to try and make a pareto graph as dynamic as possible. Not finished yet but this is the general idea:

Code:

Option Compare Database
Option Explicit

Private Sub testbutton_Click()
Dim dbs As Database
Dim ParetoQuery As QueryDef
Dim sqlstring As String
Dim sqlstring2 As String
Dim rowsource1 As String
Dim rowsource2 As String


Dim combodept As String
Dim strwhere As String
Dim lngLen As Integer

Const conJetDate = "\#mm\/dd\/yyyy\#"
Set dbs = CurrentDb
Set ParetoQuery = dbs.QueryDefs("qryPareto")

On Error GoTo ParetoError


'Lets create our WHERE string

If Not IsNull(Me.txtStartDate1) Then
strwhere = strwhere & "([DteOccur] >= " & Format(Me.txtStartDate1, conJetDate) & ") AND "
End If

If Not IsNull(Me.txtEndDate1) Then
strwhere = strwhere & "([DteOccur] < " & Format(Me.txtEndDate1, conJetDate) & ") AND "
End If



'Get rid of the trailing AND

    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strwhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "You must enter a start or end date", vbInformation, "Nothing to do."
    Exit Sub
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strwhere = Left$(strwhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
    '    Debug.Print strwhere
    End If
' End of where string creation


'The cases for our combo select, if they want to see department raised by or department responsible for the error

Select Case Me.cmbodeptstatus

Case "Raised by"
combodept = "tbllog.DeptRaisedBy"

Case "Responsible"
combodept = "tbllog.DeptResp"

Case Else
MsgBox "Please select whether you wish to base the pareto on department responsible or department raised by"

End Select



'Clear out the warnings for the deletion and insertion
DoCmd.SetWarnings False

'Lets see what they want to base the pareto on, cost figures or number of events
Select Case Me.cmboParetoBased

Case "Cost"
DoCmd.RunSQL "Delete * from tblParetoRaising"
sqlstring = "SELECT tbldept.Department, tbltypes.NCType, Sum(tblCosts.CostFig) AS SumOfCostFig, tbllog.DteOccur INTO tblParetoRaising FROM (tbldept INNER JOIN (tbltypes INNER JOIN tbllog ON tbltypes.NCtypeID = tbllog.NCType) ON tbldept.DeptID = " & combodept & ") INNER JOIN tblCosts ON tbllog.NCC_ID = tblCosts.NCC_ID WHERE (" & strwhere & ") GROUP BY tbldept.Department, tbltypes.NCType,  tbllog.DteOccur  HAVING (((tbldept.Department)=[Forms]![frmParetoRaising]![cmoDeptRaisedBy])) ORDER BY Sum(tblCosts.CostFig) DESC "
rowsource1 = "SELECT tblParetoRaising.NCType, tblParetoRaising.SumOfCostFig AS TotalCost, DSum(""[SumOfCostFig]"",""tblParetoRaising"",""[SumOfCostFig]>="" & [TotalCost] & """")/DSum(""[SumOfCostFig]"",""tblParetoRaising"") AS CPct FROM tblParetoRaising"

Case "Events"
DoCmd.RunSQL "Delete * from tblParetoRaising"
sqlstring = "SELECT tbldept.Department, tbltypes.NCType, Count(tbltypes.NCType) AS CountOfNCType, tbllog.DteOccur INTO tblParetoRaising FROM (tbldept INNER JOIN (tbltypes INNER JOIN tbllog ON tbltypes.NCtypeID = tbllog.NCType) ON tbldept.DeptID = " & combodept & ") WHERE (" & strwhere & ") GROUP BY tbldept.Department, tbltypes.NCType, tbllog.DteOccur HAVING (((tbldept.Department)=[Forms]![frmParetoRaising]![cmoDeptRaisedBy])) ORDER BY Count(tbltypes.NCType) DESC "
rowsource1 = "SELECT tblParetoRaising.NCType, tblParetoRaising.CountOfNCType AS TotalNC, DSum(""[CountOfNCType]"",""tblParetoRaising"",""[CountOfNCType]>="" & [TotalNC] & """")/DSum(""[CountOfNCType]"",""tblParetoRaising"") AS CPct FROM tblParetoRaising"

Case Else
MsgBox "Please select whether you wish to base the pareto on cost or events"

End Select

DoCmd.RunSQL sqlstring

Me.grpPareto1.Requery

'Turn the warnings back on
DoCmd.SetWarnings True

Set ParetoQuery = Nothing
Set dbs = Nothing
Exit Sub

'Handle the errors
ParetoError:
MsgBox Err.Description
Set ParetoQuery = Nothing
Set dbs = Nothing
Exit Sub
Resume

End Sub

**NeoPa** · Jul 22 '10, 01:02 PM

Originally posted by munkee

munkee: Thank you for the reply Mike I will take a look to see if I can remove it.

It appears you already have. There are no signs of it in your recently posted code. It was possibly somewhat confusing as your error message didn't match the code you posted originally.

It appears that all is working now though, so all is good.

Syntax error (missing operator) when setting query sql

Syntax error (missing operator) when setting query sql

Comment

Comment

Comment

Comment