Show Zero in column instead of Blank 2

[netrusher]

Below is the code for my query. The columns are by week. One column is a GrandTotal of all the weeks that are displayed.
The other columns are by week and show the total number of problems by week. My issue is
as follows. Some weeks there are no problems leaving the column blank. I would like to show 0
if there were no problems for any particular week. Does anyone know how I can accomplish that?

PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime, [Forms]![Queries_ReportsFRM].[FaultCategory] Text ( 255 ), [Forms]![Queries_ReportsFRM].[SystemGroupProblem] Text ( 255 );
TRANSFORM Sum([Trends-1-3TON-WEEK].Totals) AS SumOfTotals1
SELECT [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, [Trends-1-3TON-WEEK].Problem, Sum([Trends-1-3TON-WEEK].Totals) AS GrandTotal
FROM [Trends-1-3TON-WEEK]
GROUP BY [Trends-1-3TON-WEEK].SystemGroup, [Trends-1-3TON-WEEK].FaultCategory, [Trends-1-3TON-WEEK].Problem
PIVOT [Trends-1-3TON-WEEK].YearMonthWeek;

 

[dhookom]

You can show a 0 in a form or report by taking advantage of the Format property. If you want to display a 0 in the actual query, try

TRANSFORM Val(Nz(Sum([Totals]),0)) AS SumOfTotals1

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[netrusher]

Duane,

That worked great and thanks!!!!

 

[PHV]

Doesn't the following suffice ?
TRANSFORM Nz(Sum([Totals]),0) AS SumOfTotals1

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

 

[netrusher]

That works too!!

Thanks. I do not really understand the difference between the two but appreciate the help.

 

[PHV]

In fact, the magic stuff is the Nz function ...

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

 

[netrusher]

The last one aligns my numbers to the left where before they are aligned to the right. The totals are the same.

 

[PHV]

OK, I now understand why Duane in his wisdom used the Val function to coerce a numeric value !

 

[dhookom]

I've been burned when attempting to sum the results of Nz() in a report. In most instances, I attempt to be as explicit as possible in code and expressions.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]