Display zero values in a crosstab query (1 Viewer)

petewilson44 · May 27, 2009

Is there any way a crosstab query will display a full table even if there are limited records.

i.e. for a table that holds actions submitted to a certain department - where actions were submitted on mon, tues, wed, fri, sat, but not thursday or sunday

at the moment the crosstab will look like this:

date count
monday 15
tuesday 8
wednesday 6
friday 2
saturday 3

which screws up the report as the template headings display each day

could i get the crosstab to display:

date count
monday 15
tuesday 8
wednesday 6
thursday 0
friday 2
saturday 3
sunday 0

or a variation around this......???

DCrake · May 28, 2009

If you go to the properties of the column field that displays the data and select Column Headings then type in the days of the week "Monday","Tuesday","etc" then run the query it will force the headings to appear even iof there is no data present.

David

petewilson44 · May 28, 2009

ha, strange that you should post that literally 10 seconds after i stumbled upon it whilst searching through the properties.....however.....it gives the headings I want/need, but doesnt display any data!

so i can have data without headings....and headings without data.......maybe i should just send 2 reports every day......as well as a pair of scissors and some pritt stick!!

petewilson44 · May 28, 2009

no! IT WORKS!!!!!!!!!!!!!!!!!

hmmmm, must have bin me being an idiot......[big sigh].....that has saved me a lot of bodging, fudging, hacking, stressing, explaining, crying, screaming, slapping, sweating and s##tting!!!

cheers guys

namliam · May 28, 2009

Crosstabs dont show 0 where there is a NULL value...

What you can do if you have a limited set of fields like this from your crosstab.... is build a query on top of the crosstab replacing null values by 0.

Select NZ(YourMonday,0) as New0Monday
from YourCrosstabQuery

ANother alternative could be to make a dummy table with always 0 in every day, this you then UNION to your original data...

DCrake · May 28, 2009

You must have types in the headings incorrectly. cut them out and re run the query and look at how the headings are displayed/formatted and replicate this in your column headings.

petewilson44 · May 28, 2009

nil valuesa are fine as long as the headings are there - reason being, the report that is generated is a sub report of a bigger, greater, stronger, prettier, fuller-figured, mega-super-duper-wooper report........and i need the headings to line up

thats cool though, nil values are fine, i can live without "zeros" in my life (which coincidentally enough is the name of a gay club in my town) - so i guess that staement has a double meaning

cheers again guys for all your help! give yourselves a pat on the back, buy yourselves an ice-cream and send me the bill!!

DCrake · May 28, 2009

If you actually want zeros to appear instead of nothing then wrap Nz([fieldName],0) around your column heading field

METSKER · Jan 25, 2017

You can display zeros!!!

Set up the value field as follows:

Field--- VALUE: IIf(IsNull(Count(

![field]))=True,0,Count(

![field]))

Table--- it will be blank since defined the table in "Field"
Total--- this is important!!! it must be blank!!! because you defined the total in "Field"
Crosstab--- Value

Display zero values in a crosstab query (1 Viewer)

petewilson44

New member

DCrake

Remembered

petewilson44

New member

petewilson44

New member

namliam

The Mailman - AWF VIP

DCrake

Remembered

petewilson44

New member

DCrake

Remembered

METSKER

New member

Similar threads

Users who are viewing this thread