Need Zeros, Too

[ckirmser]

So, I've gotten my query to Sum a field according to Group By criteria. Simple enough, now that I've seen how it's done.

But, the summed data is not part of a table, it's a calculated value, like this;

SELECT [Light Duty].ID, [Light Duty].lname, [Light Duty].fname, [Light Duty].empno, [Light Duty].doi, [Light Duty].dateldbegan, [Light Duty].dateldend, DateDiff("d",[dateldbegan],[dateldend]) AS daysonld
FROM [Light Duty]
WHERE ((([Light Duty].dateldend) Is Not Null))
ORDER BY [Light Duty].ID;

So, when I use this grouped query;

SELECT [Closed Light Duty Records].empno, [Closed Light Duty Records].doi, Sum([Closed Light Duty Records].daysonld) AS SumOfdaysonld
FROM [Closed Light Duty Records]
GROUP BY [Closed Light Duty Records].empno, [Closed Light Duty Records].doi
ORDER BY [Closed Light Duty Records].empno;

It gives me a list of records that have non-zero values in SumOfdaysonld. But, when I use this query in a report, it confines the report to only those entries where SumOfdaysonld is non-zero. I need to list the zero records, too.

Essentially, I need a default value for SumOfdaysonld of zero. I can do that in a table, but how do I do it for a query where the valued is calculated?

 

[GohDiamond]

WHERE ((([Light Duty].dateldend) Is Not Null))

This tells the query to ignore records with a null in this field. But you seem to indicate that you need the records with the NULL field too. You can define the result of a Null field as zero (0) using Nz([field that may have null values],0)

Here's an example for your query:
DateldendZ:=Nz([Light Duty].dateldend,0)
The field is renamed ateldendZ because it can't have the same name as an existing field name
Nz changes the null values in [Light Duty].dateldend to zero

Hope that give you a way to go,
Cheers
Goh

 

[ckirmser]

Many thanx, Goh!

I'll give that a try...