What to do when crosstab doesnt produce a result?

[BeruthialsCat]

Hi all

Hope you can help i have become completely stumped bu a crosstab query
that produces no results. Its expected that it wont everynow and then
and in those case i need to produce a row of zeros. I remember seeing a
post where somone had unioned a row to the crosstab which would be
displayed only when the crosstab didnt produce rows but i cant remember
how to do it. Any ideas?

 

[Duane Hookom]

Are the column headings always known? Have you entered a value into the
Column Headings property? If not I doubt you will have much luck doing this
in a query.

 

[BeruthialsCat]

Yuppers Jan to Dec always known. Worked a solution. I assign the
rowsource properties of loads of subforms before the form is displayed
so i just check if it has results and if it doesnt i put in a row of
zeros, may not be the best solution but it works

as the column headings are always known did u have a potential
solution?

 

[Duane Hookom]

I could have worked out a solution is the number and type of columns was
known. However, since you seem to have found a solution, I won't spend the
time.

 

[BeruthialsCat]

Duane,

May i ask that you could give me a pointer in the direction of a
solution. as to how to join a blank row to a crosstab that fails
because it would be of immense future use to me for other projects we
are doing here at work. Much of it is to do with displaying on forms
the results of crosstabs in subforms. My solution is clunky requires
every subform and main form to have code in them whereas a simple union
could be appended to every crosstab as it was created removing the need
to use the code at all.

Happy for any help even the merest hint of a direction like read the
section blah blah blah and if u get stuck ask again in the news group.
I just dont know where to start looking.

Many Thanks

David

 

[Duane Hookom]

I can't imagine one instance where I would want to show 0s where no records
exist. It doesn't make much sense one time, much less "immense future use".
I guess I would create a "dummy" subform based on a table with fields of the
months and a single record with 0s. Place the subform overtop the original
subform and then make either visible based on if the original subform
returns records.

 

[BeruthialsCat]

At least i gave u the chance to sneer and and as for the idea of a
solution dummy subforms hide and showing? my changeing the
recordsource at runtime now seems quite elegent.

Ty