How to combine these two elements into a select query

[leifoet]

How can I make the SUM of a numeric field (Number_2017) in which NULL records and BLANK records counted as 0

I tried this for the NULL-records :
SELECT sum(IIF(ISNULL (Number_2017),0,Number_2017)) AS TotalNumber FROM Tablematch
but how to combine it with the 'blanks' (=string) ?

Thanks - Leifoet

 

[dhookom]

You should be able to use:

SELECT sum(Number_2017) AS TotalNumber 
FROM Tablematch;

If you have all nulls, you can try:

SELECT Nz(sum(Number_2017),0) AS TotalNumber 
FROM Tablematch;

Having a field named Number_2017 suggests there might be a Number_2016 etc which is typically not good table design.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

 

[PizMac]

I don't understand - if you're doing a sum making nulls or blanks zero will have no effect on the end result? perhaps you meant to count not sum? in which case

SELECT Count(IIf(IsNull([Number_2017]),0,IIf(([Number_2017])="",0,[Number_2017]))) AS TotalNumber
FROM Tablematch;

 

[dhookom]

I agree there seems to be a misunderstanding however:

SELECT Count(IIf(IsNull([Number_2017]),0,IIf(([Number_2017])="",0,[Number_2017]))) AS TotalNumber
FROM Tablematch;

is the same as

SELECT Count(*) AS TotalNumber
FROM Tablematch;

If Number_2017 is a numeric field, it will never be "". The simple solution for changing a Null to 0 is by using Nz(Number_2017,0).

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016