Solved Fill in 0 in crosstab query (1 Viewer)

Kayleigh

Member
Local time
Today, 08:32
Joined
Sep 24, 2020
Messages
709
Hi
I have the following crosstab query which summarises the sale figures for each day. However I would like to have 0s in place of blanks.
1703523724088.png


When I try Nz() or IIF(null()) it doesn't work - probably because there are no rows to summarise for those days.

Any ideas how I can do this?
 

plog

Banishment Pending
Local time
Today, 02:32
Joined
May 11, 2011
Messages
11,675
You need a datasource that generates those permutations of Dates and Sales Types.

Do you have a table with all the Dates you want to report on? Do you have a table with all the SAles Types you want to report on? What are the table names and corresponding field names?
 

Kayleigh

Member
Local time
Today, 08:32
Joined
Sep 24, 2020
Messages
709
I don't have table of all dates - dates shown are generated from date sale is created.

Yes I do have table of sales types - SupplyFit
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:32
Joined
Oct 29, 2018
Messages
21,581
Would you mind posting the sql statement for your crosstab query?
 

Kayleigh

Member
Local time
Today, 08:32
Joined
Sep 24, 2020
Messages
709
This is the CT query:

SQL:
TRANSFORM Sum(qryDailyLeadsShort.CountOffldOrderID) AS SumOfCountOffldOrderID
SELECT lkptblDays.fldDay AS [Day], Format([cfdate],"dd-mmm") AS [Date]
FROM (lkptblSupplyFit INNER JOIN qryDailyLeadsShort ON lkptblSupplyFit.fldSupplyFitID = qryDailyLeadsShort.fldOSupplyFitID) INNER JOIN lkptblDays ON qryDailyLeadsShort.cfDay = lkptblDays.fldDayID
GROUP BY lkptblDays.fldDay, Format([cfdate],"dd-mmm")
ORDER BY Format([cfdate],"dd-mmm")
PIVOT lkptblSupplyFit.fldSupplyFit;

This is the underlying summary query:
SQL:
SELECT Count(qryDailyLeadsFilter.fldOrderID) AS CountOffldOrderID, qryDailyLeadsFilter.fldOSupplyFitID, CDate(Format([fldocreated],"Short Date")) AS cfDate, Weekday([fldocreated]) AS cfDay
FROM qryDailyLeadsFilter
GROUP BY qryDailyLeadsFilter.fldOSupplyFitID, CDate(Format([fldocreated],"Short Date")), Weekday([fldocreated]);
 

CJ_London

Super Moderator
Staff member
Local time
Today, 08:32
Joined
Feb 19, 2013
Messages
16,704
Use the format property to show 0 instead of null

it would be

;;;0

Or perhaps
;;;”0”
 

Users who are viewing this thread

Top Bottom