Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    adoR.Open "SELECT L0928.PROVA03,L0928.PROVA05,L0928.PROVA01,L0928.PR OVA02, Count(L0928.PROVA05) AS SOMMA5 FROM L0928 GROUP BY L0928.PROVA03, L0928.PROVA05 WHERE PROVA01 = '" & Var_DATA & "' AND PROVA08 = '" & COD_AREA & "' ORDER BY PROVA08,PROVA03,PROVA05", adoConn

    have error in "operator"!!!!

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Suggest you define a string variable to contain the SQL Statement so you can debug it and see what is actually being passed to the database engine. For example:
    Code:
    dim strMySQL
    ...
    strMySQL = "SELECT L0928.PROVA03,L0928.PROVA05,L0928.PROVA01,L0928.PROVA02, Count(L0928.PROVA05) AS SOMMA5 FROM L0928 GROUP BY L0928.PROVA03, L0928.PROVA05 WHERE PROVA01 = '" & Var_DATA & "' AND PROVA08 = '" & COD_AREA & "' ORDER BY PROVA08,PROVA03,PROVA05"
    adoR.Open strMySQL, adoConn
    Then you can run in debug mode and print the results in the immediate window in VBA, and then paste that into a Query and see where the wheels fall off.
    Wendell

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='WendellB' post='791818' date='02-Sep-2009 17:33']Suggest you define a string variable to contain the SQL Statement so you can debug it and see what is actually being passed to the database engine. For example:
    Code:
    dim strMySQL
    ...
    strMySQL = "SELECT L0928.PROVA03,L0928.PROVA05,L0928.PROVA01,L0928.PROVA02, Count(L0928.PROVA05) AS SOMMA5 FROM L0928 GROUP BY L0928.PROVA03, L0928.PROVA05 WHERE PROVA01 = '" & Var_DATA & "' AND PROVA08 = '" & COD_AREA & "' ORDER BY PROVA08,PROVA03,PROVA05"
    adoR.Open strMySQL, adoConn
    Then you can run in debug mode and print the results in the immediate window in VBA, and then paste that into a Query and see where the wheels fall off.[/quote]

    Sorru me... but i use VBA for excel, how to?

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Sorry - since you posted this in the Access forum, I presumed you were using Access. If you are doing queries from Excel, I'm not much help.
    Wendell

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    In a totals query, you MUST apply a summary function such as Sum, Count, Min, etc. to each field in the SELECT part on which you do not group.
    You group by PROVA03 and PROVA05, so you must apply a summary function to PROVA01 and PROVA02 if you want to include them in the SELECT part.

  6. #6
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='793642' date='17-Sep-2009 02:06']In a totals query, you MUST apply a summary function such as Sum, Count, Min, etc. to each field in the SELECT part on which you do not group.
    You group by PROVA03 and PROVA05, so you must apply a summary function to PROVA01 and PROVA02 if you want to include them in the SELECT part.[/quote]
    Tks for suggestion.
    Sal

Posting Permissions

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