Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2004
    Location
    Bollnäs, Sweden
    Posts
    3

    Question Unanswered: Sum and divide but it contains a "null" value

    I´m working in excel and getting the information from a access database with the "import external data" with a database query.

    Problem: trying to divide with "StatArt.aPris" but it contains a "null" value.
    Errormessage: Numeric Value is Outside the interval (null)

    SELECT
    Sum((StatArt.aPris*StatArt.Antal) / StatArt.aPris) AS 'TB'
    FROM...


    I´m a beginner and need some help

    Thanks

    Magnus

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Wesslan
    I´m working in excel and getting the information from a access database with the "import external data" with a database query.

    Problem: trying to divide with "StatArt.aPris" but it contains a "null" value.
    Errormessage: Numeric Value is Outside the interval (null)

    SELECT
    Sum((StatArt.aPris*StatArt.Antal) / StatArt.aPris) AS 'TB'
    FROM...


    I´m a beginner and need some help

    Thanks

    Magnus
    What value do you want to divide by if it is null??? 1? Try something like:

    NZ(StatArt.aPris,1)
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Oct 2004
    Location
    Bollnäs, Sweden
    Posts
    3

    The code

    Tried this:

    Sum(StatArt.Antal * StatArt.aPris) / NZ(StatArt.aPris,0) AS 'TB'

    This gave this error message: undefined function 'NZ' in expression




    THIS IS IT ALL

    The first works, Antal and Totalt. But when I am trying to divide with StatArt.aPris i contains Null but should be 0 (cause the salesprice was 0 for some of those Artikels)

    -----------------------
    SELECT DISTINCT StatArt.Artikelnr, Art.Benämning_0 AS 'Artikel', Sum(StatArt.Antal) AS 'Antal', Sum(StatArt.aPris*StatArt.Antal) AS 'Totalt',

    Sum((StatArt.Antal * StatArt.aPris,1) / StatArt.aPris) AS 'TB'

    FROM `P:\FDT\DATA\Avance\Fakt000`.Art Art, `P:\FDT\DATA\Avance\Fakt000`.Kund Kund, `P:\FDT\DATA\Avance\Fakt000`.StatArt StatArt
    WHERE StatArt.Artikelnr = Art.ArtikelNr AND StatArt.Kundnr = Kund.KundNr AND ((Art.ArtikelNr Like '%6161%') AND (Kund.Kundgrupp=0) AND (StatArt.Datum Between {ts '2003-01-01 00:00:00'} And {ts '2003-12-31 00:00:00'}))
    GROUP BY StatArt.Artikelnr, Art.Benämning_0
    --------------------------------------------

Posting Permissions

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