# Thread: Sum and divide but it contains a "null" value

1. Registered User
Join Date
Oct 2004
Location
Bollnäs, Sweden
Posts
3

## 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. Grand Poobah
Join Date
Sep 2003
Location
MI
Posts
3,713
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)

3. Registered User
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
•