show null value as zero 2

[LloydDobler]

I have a query I need to show the value as zero '0' if the value returned in the query is null. Any help is appreciated.

"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[Remou]

You need Nz.
Nz(Field1,0)

 

[MichaelRed]

convertinf null to zero is a generally bad idea fraught with pitfalls. even though you say it is for 'display' purposes, you need to be quite sure that you, at least, know the full meaning of null, even after that, someone will eventually try to find the averag / mean or other stat - and your 'display' value will cause it to go awry. just let it be blank. then, you at least have a defensible position when jane calculator acuses you of having it wrong (at least if you go find out the meaning of 'Null'(




MichaelRed

 

[r937]

agree with michaelred's comments

r937.com | rudy.ca

 

[Remou]

Ok. Most documents have something to indicate a missing value, so perhaps LloydDobler can use:
[tt]Nz(fld, "N/A")[/tt]
Or
[tt]Nz(fld, "-")[/tt]

 

[MichaelRed]

" ... Most documents have something to indicate a missing value ... " [\quote]

Really? I thought that just plain empty did nicely ... no need to go hunting for trouble and / or exceptions ...




MichaelRed

 

[Golom]

The problem with something like

Nz(fld, "N/A")

is that, if "fld" is a numeric then Access will choke because it's trying to put numeric and text values in the same column.

You would need to do something like

IIF ( IsNull(fld), 'N/A', Format ( fld, "0") )

which is much messier than just allowing a blank (i.e. NULL) field.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Remou]

Ok, 'most' is far too strong, but in financial statements (eg.

http://www.va.gov/budget/report/2005/ConsolidatedFinancialStatements.pdf)

a dash is common, and N/A is often used in statistical tables (eg.

http://www.nsf.gov/statistics/gradpostdoc/94dst/c/tab/c16.htm)

A symbol of some description, rather than a blank, can make a document easier to read.

I found that this worked in Access, even though organizationID is numeric, but the column was converted to text:
[tt]SELECT Nz([OrganizationID],"N/A") AS Expr1
FROM tblTickets;[/tt]

However, I have wandered away from the point of this forum as I suppose all this is only relevant if the query is to be used in a report.

 

[LloydDobler]

Okay, having a little trouble with this one...Will Nz() work on the statement below?

Sum(qry_ontime_currentyear.ontime/qry_OnTime_TotalDelivered_currentyear.totaldelivered)*100 AS PercentOnTime

"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[Remou]

I think that there may be a problem. Does qry_OnTime_TotalDelivered_currentyear.totaldelivered always have a value?

 

[r937]

you probably won't need it there

SUM() is an aggregate function, so it ignores NULLs

the only way you might need it is if every single row you're summing has bad data

r937.com | rudy.ca

 

[LloydDobler]

r937, i'm not getting values when I try that calculation though. Here's my query followed by output.

SELECT qry_OnTime_TotalDelivered_currentyear.QZWKNR, qry_OnTime_TotalDelivered_currentyear.QZLINR, Sum(qry_ontime_currentyear.ontime/qry_OnTime_TotalDelivered_currentyear.totaldelivered)*100 AS PercentOnTime, qry_OnTime_TotalDelivered_currentyear.TOTALDELIVERED, IIf(IsNull(qry_ontime_currentyear.OnTime),0,qry_ontime_currentyear.OnTime) AS ontime

FROM qry_OnTime_TotalDelivered_currentyear LEFT JOIN qry_ontime_currentyear ON (qry_OnTime_TotalDelivered_currentyear.QZWKNR = qry_ontime_currentyear.QZWKNR) AND (qry_OnTime_TotalDelivered_currentyear.QZLINR = qry_ontime_currentyear.QZLINR)

GROUP BY qry_OnTime_TotalDelivered_currentyear.QZWKNR, qry_OnTime_TotalDelivered_currentyear.QZLINR, qry_OnTime_TotalDelivered_currentyear.TOTALDELIVERED, qry_ontime_currentyear.OnTime;

Outputs as...

Plant CUST# %OnTime #DELIVERED #ontime
001 90068 55 20 11
001 90086 50 4 2
001 90186 22.53 31 7
001 90203 82.7 81 67
001 90209 70.5 17 12
001 90211 47.4 59 28
001 90232 75 4 3
002 90258 70.5 17 12
002 90272 1 0
002 90274 100 13 13
002 90294 79.3 29 23
002 90311 83.3 12 10



"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[r937]

select TD.QZWKNR
     , TD.QZLINR
     , sum( 
       [b]iif(isnull(OT.OnTime),0
                 ,OT.OnTime)[/b]
          / TD.TOTALDELIVERED
          ) *100        as PercentOnTime
     , TD.TOTALDELIVERED
     , iif(isnull(OT.OnTime),0
                 ,OT.OnTime) as ontime
  from qry_OnTime_TotalDelivered_currentyear 
          as TD
left 
  join qry_ontime_currentyear 
          as OT 
    on TD.QZWKNR = OT.QZWKNR 
   and TD.QZLINR = OT.QZLINR
group 
    by TD.QZWKNR
     , TD.QZLINR
     , TD.TOTALDELIVERED
     , OT.OnTime

r937.com | rudy.ca

 

[LloydDobler]

That worked r937, thanks. It makes sense to have the iif statement in the original sum expression as well!

"I don't want to sell anything, buy anything or process anything as a career. I don't want to sell anything bought or processed... or buy anything sold or processed... or process anything sold, bought or processed... or repair anything sold, bought or processed. You know, as a career, I don't want to do that."

 

[PHV]

The shorter version of:
iif(isnull(OT.OnTime),0
,OT.OnTime)
is:
Nz(OT.OnTime),0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r937]

thanks PHV, i was not sure if that was the case

the reference i'm using says NZ(field,char) and i wasn't sure it would pass a numeric as well as a char

by the way, you gots an extra paren in there ;-)


r937.com | rudy.ca

 

[PHV]

OOps, good catch Rudy :~/

 

[PHV]

I think it's time to go to bed for me ...
I forgot to post the right syntax:
Nz(OT.OnTime,0)

 

[MichaelRed]

... but then the expression is doing exactly the corruption you DONT want. by counting the null as a zero, you change the percentage calculation!!

NULL means you DO NOT KNOW, the expression is essientially saying you DO know ... and there are no on times for the unit / record!!!

If you insist on mucking about with nulls and aggregates, you need to really -and carefully- think it through.

the entire expression should be constructed to NOT return a value or when the predicate is null. showing the results as zero is entirely wrong as well as misleading.






MichaelRed