By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,780 Members | 1,520 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,780 IT Pros & Developers. It's quick & easy.

Syntax error (missing operator) when setting query sql

100+
P: 374
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:

Expand|Select|Wrap|Line Numbers
  1. 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]]","tblParetoRaising","[SumOfCostFig]>=" & [TotalCost] &")/DSum("[SumOfCostFig]","tblParetoRaising") AS CPct FROM tblParetoRaising;'.

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

Expand|Select|Wrap|Line Numbers
  1. rowsource1 = "SELECT tblParetoRaising.NCType, tblParetoRaising.SumOfCostFig AS TotalCost, DSum(""[SumOfCostFig]"",""tblParetoRaising"",""[SumOfCostFig]>="" & [TotalCost] & "")/DSum(""[SumOfCostFig]"",""tblParetoRaising"") AS CPct FROM tblParetoRaising;"
  2.  
But I still get the same error. Any ideas?

The code I use to update is below:

Expand|Select|Wrap|Line Numbers
  1. Dim dbs As Database
  2. Dim ParetoQuery As QueryDef
  3. Dim sqlstring As String
  4. Dim sqlstring2 As String
  5. Dim rowsource1 As String
  6.  
  7. Set dbs = CurrentDb
  8. Set ParetoQuery = dbs.QueryDefs("qryPareto")
  9.  
  10. rowsource1 = "SELECT tblParetoRaising.NCType, tblParetoRaising.SumOfCostFig AS TotalCost, DSum(""[SumOfCostFig]"",""tblParetoRaising"",""[SumOfCostFig]>="" & [TotalCost] & "")/DSum(""[SumOfCostFig]"",""tblParetoRaising"") AS CPct FROM tblParetoRaising;"
  11.  
  12.  
  13. ParetoQuery.SQL = rowsource1
  14. dbs.QueryDefs.Refresh
  15.  
  16. Me.grpPareto1.Requery
  17.  
  18. Set ParetoQuery = Nothing
  19. Set dbs = Nothing
  20. Exit Sub
Jul 22 '10 #1

✓ answered by munkee

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:

Expand|Select|Wrap|Line Numbers
  1. ","[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 =]

Share this Question
Share on Google+
4 Replies


100+
P: 374
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:

Expand|Select|Wrap|Line Numbers
  1. ","[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 =]
Jul 22 '10 #2

Expert 100+
P: 634
@munkee
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
Expand|Select|Wrap|Line Numbers
  1. rowsource1 = "SELECT tblParetoRaising.NCType, tblParetoRaising.SumOfCostFig AS TotalCost, DSum(""[SumOfCostFig]"",""tblParetoRaising"",""[SumOfCostFig]>=" & [TotalCost] & ")/DSum(""[SumOfCostFig]"",""tblParetoRaising"") AS CPct FROM tblParetoRaising;"
??


MTB
Jul 22 '10 #3

100+
P: 374
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:

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Sub testbutton_Click()
  5. Dim dbs As Database
  6. Dim ParetoQuery As QueryDef
  7. Dim sqlstring As String
  8. Dim sqlstring2 As String
  9. Dim rowsource1 As String
  10. Dim rowsource2 As String
  11.  
  12.  
  13. Dim combodept As String
  14. Dim strwhere As String
  15. Dim lngLen As Integer
  16.  
  17. Const conJetDate = "\#mm\/dd\/yyyy\#"
  18. Set dbs = CurrentDb
  19. Set ParetoQuery = dbs.QueryDefs("qryPareto")
  20.  
  21. On Error GoTo ParetoError
  22.  
  23.  
  24. 'Lets create our WHERE string
  25.  
  26. If Not IsNull(Me.txtStartDate1) Then
  27. strwhere = strwhere & "([DteOccur] >= " & Format(Me.txtStartDate1, conJetDate) & ") AND "
  28. End If
  29.  
  30. If Not IsNull(Me.txtEndDate1) Then
  31. strwhere = strwhere & "([DteOccur] < " & Format(Me.txtEndDate1, conJetDate) & ") AND "
  32. End If
  33.  
  34.  
  35.  
  36. 'Get rid of the trailing AND
  37.  
  38.     'See if the string has more than 5 characters (a trailng " AND ") to remove.
  39.     lngLen = Len(strwhere) - 5
  40.     If lngLen <= 0 Then     'Nah: there was nothing in the string.
  41.         MsgBox "You must enter a start or end date", vbInformation, "Nothing to do."
  42.     Exit Sub
  43.     Else                    'Yep: there is something there, so remove the " AND " at the end.
  44.         strwhere = Left$(strwhere, lngLen)
  45.         'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
  46.     '    Debug.Print strwhere
  47.     End If
  48. ' End of where string creation
  49.  
  50.  
  51. 'The cases for our combo select, if they want to see department raised by or department responsible for the error
  52.  
  53. Select Case Me.cmbodeptstatus
  54.  
  55. Case "Raised by"
  56. combodept = "tbllog.DeptRaisedBy"
  57.  
  58. Case "Responsible"
  59. combodept = "tbllog.DeptResp"
  60.  
  61. Case Else
  62. MsgBox "Please select whether you wish to base the pareto on department responsible or department raised by"
  63.  
  64. End Select
  65.  
  66.  
  67.  
  68. 'Clear out the warnings for the deletion and insertion
  69. DoCmd.SetWarnings False
  70.  
  71. 'Lets see what they want to base the pareto on, cost figures or number of events
  72. Select Case Me.cmboParetoBased
  73.  
  74. Case "Cost"
  75. DoCmd.RunSQL "Delete * from tblParetoRaising"
  76. 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 "
  77. rowsource1 = "SELECT tblParetoRaising.NCType, tblParetoRaising.SumOfCostFig AS TotalCost, DSum(""[SumOfCostFig]"",""tblParetoRaising"",""[SumOfCostFig]>="" & [TotalCost] & """")/DSum(""[SumOfCostFig]"",""tblParetoRaising"") AS CPct FROM tblParetoRaising"
  78.  
  79. Case "Events"
  80. DoCmd.RunSQL "Delete * from tblParetoRaising"
  81. 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 "
  82. rowsource1 = "SELECT tblParetoRaising.NCType, tblParetoRaising.CountOfNCType AS TotalNC, DSum(""[CountOfNCType]"",""tblParetoRaising"",""[CountOfNCType]>="" & [TotalNC] & """")/DSum(""[CountOfNCType]"",""tblParetoRaising"") AS CPct FROM tblParetoRaising"
  83.  
  84. Case Else
  85. MsgBox "Please select whether you wish to base the pareto on cost or events"
  86.  
  87. End Select
  88.  
  89. DoCmd.RunSQL sqlstring
  90.  
  91. Me.grpPareto1.Requery
  92.  
  93. 'Turn the warnings back on
  94. DoCmd.SetWarnings True
  95.  
  96. Set ParetoQuery = Nothing
  97. Set dbs = Nothing
  98. Exit Sub
  99.  
  100. 'Handle the errors
  101. ParetoError:
  102. MsgBox Err.Description
  103. Set ParetoQuery = Nothing
  104. Set dbs = Nothing
  105. Exit Sub
  106. Resume
  107.  
  108. End Sub
  109.  
Jul 22 '10 #4

NeoPa
Expert Mod 15k+
P: 31,470
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.
Jul 22 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.