Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2012
    Posts
    1

    Unanswered: syntax error (missing operator) in query expression.....

    Hi All,

    I am trying to format my access sql queries in vba...below is my statement.

    I keep getting an error that I have a missing operator in 'Format(Sum[Qty_Sent],'Standard')'

    Any help would be greatly appreciated.

    strSQL1 = "SELECT [client_id], Format(Sum[Qty_Sent],'Standard') AS Qty_Sent, Sum[Qty_Filled] AS Qty_Filled, [Qty_Filled]/[Qty_Sent] AS Fill_Rate " & _
    "FROM " & Table & " " & _
    "GROUP BY [Destination], [client_id] " & _
    "HAVING ((([Destination])='xxxx'));"

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I'm not sure that the Jet Engine is able to understand 'Standard' as a valid format. Try using a format string such as: "#,##0.00" instead. See: Format Property - Number and Currency Data Types - Access - Office.com
    Have a nice day!

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    I, too, have serious doubts about the use of 'Standard', but putting that aside for a moment, you have another problem:

    Format(Sum[Qty_Sent],'Standard')

    needs to be

    Format(Sum([Qty_Sent]),'Standard')

    and again,

    Sum[Qty_Filled]

    needs to be

    Sum([Qty_Filled])

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

Posting Permissions

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