Solved Fill in 0 in crosstab query (1 Viewer)

[Kayleigh]

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.

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]

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]

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]

Would you mind posting the sql statement for your crosstab query?

 

[Kayleigh]

This is the CT query:

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:

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]

Use the format property to show 0 instead of null

it would be

;;;0

Or perhaps
;;;”0”

 

[June7]

Got this to work: #;;;0

 

[Kayleigh]

That's so cool

 

[Kayleigh]

Got this to work: #;;;0

How could I do the same for a currency field?

 

[CJ_London]

Take a look at this link

Format a number or currency field - Microsoft Support

Custom formats for numbers and currency fields in Access can be useful when you want the data displayed in a specific way.

support.microsoft.com

 

[Kayleigh]

My solution was: £#,##0.00;(£#,##0.00);\0;"£0.00"

FYI this was very useful in explaining

Format Property - Number and Currency Data Types - Microsoft Access Visual Basic Documentation

documentation.help

 

[CJ_London]

Happy to help