Good morning

In the Crosstab Query SQL listed below...how can I get it to show 0 (Zero) if there is no data for that day of the week?

TRANSFORM Sum(qryTransactionsWeekly.Loads) AS SumOfLoads
SELECT qryTransactionsWeekly.FirstDayofWeek, qryTransactionsWeekly.LastDayofWeek, Sum(qryTransactionsWeekly.Loads) AS TotalLoads
FROM qryTransactionsWeekly
GROUP BY qryTransactionsWeekly.FirstDayofWeek, qryTransactionsWeekly.LastDayofWeek
ORDER BY qryTransactionsWeekly.FirstDayofWeek DESC
PIVOT qryTransactionsWeekly.TWeekday In ("Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday");


Thanks in advance
Larry

A couple of approaches ...

1. Have you looked into using the Nz() function?
2. Have you created a set of 'placeholder' values (e.g., Monday, 0, Tuesday, 0, ...) and joined to that?

Good luck!

to:

The Sum needs to be in the inside and the Nz the outside. Like the following:

Thanks to the 3 of you for your replies

That got my zero if there is no data...but it has taken the format off as well. On one of them I need it to show as a Fixed number with 2 (two) decimal places like 12.25

Thanks again
Larry

Larry

"Wrap" that with a Format() expression.

this will format your number to 2 decimal places:

or

Thanks to everyone

The first one in arnelgp's reply did it.

Thanks
Larry...

From Jeff and Larry, we are happy to be of helped to you.