Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Grouping Query Records (2000)

    I'm trying to sum and average three fields in the attached QRend2Mens query by grouping on month and year but I get an error message when I run the following(QRend2Mensx) query.

    <font color=red>SELECT Sum(G2Dez.Energia) AS SommaDiEnergia, Avg(Val(qx(Potmedia(G2dez.Energia,g2dez.Ore_marc),[QT],Potmedia(G3dez.Energia,g3dez.Ore_marc)))) AS PORT2, Sum(G2Dez.ORE_MARC) AS SommaDiORE_MARC, Month(g2dez.giorno) AS Espr1, Year(g2dez.giorno) AS Espr2
    FROM (PortDez23 INNER JOIN G2Dez ON PortDez23.Giorno = G2Dez.GIORNO) INNER JOIN G3Dez ON PortDez23.Giorno = G3Dez.GIORNO
    WHERE (((G2Dez.GIORNO) Between DateSerial([forms]![Mseledata1]![anno],[forms]![Mseledata1]![mese],1) And DateSerial([forms]![Mseledata1]![anno1],[forms]![Mseledata1]![mese1],gmese([forms]![Mseledata1]![anno1],[forms]![Mseledata1]![mese1]))))
    GROUP BY Month(g2dez.giorno), Year(g2dez.giorno);</font color=red>

    What can I change to make it work?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Grouping Query Records (2000)

    There are a few records for which qx(PotMedia(...)) returns Null; the Val function can't handle that. Use Nz to replace Null by 0:

    Avg(Val(Nz(qx(Potmedia([G2dez].[Energia],[g2dez].[Ore_marc]),[QT],Potmedia([G3dez].[Energia],[g3dez].[Ore_marc])),0)))

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Grouping Query Records (2000)

    Thank you Hans

Posting Permissions

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