Crosstab Report

[Guest]

I have a crosstab query that works great. But I need to print out a report.
The fields in the query do not change, just whether there is data or not.
Explination:

table - used to enter data on
query1 - sorts data per week (week 16)
crosstab - sorts query1 per day (sun thru sat of week 16 with totals per day
and per week)
report - displays crosstab

Even though I know that there will always be seven days in the week,
sometimes data is not entered. I want to be able to display the blank column
in the report for the day missing.

I have tried to put an if then function(=if(iserror([mon]),null,[mon])) but
then nothing shows on the report at all even if there is data to display. I
have even tried ismissing, isnull and any others that I can think of.

Is there another way to leave that day blank but still display the report
without erroring?

 

[Guest]

Edit the SQL like this --
TRANSFORM ...
.....
PIVOT XXX IN("SUN", "MON", "TUE", "WED", "THR", "FRI", "SAT");

 

[Guest]

Thanks it works great now.

Edit the SQL like this --
TRANSFORM ...
....
PIVOT XXX IN("SUN", "MON", "TUE", "WED", "THR", "FRI", "SAT");

--
KARL DEWEY
Build a little - Test a little

I have a crosstab query that works great. But I need to print out a report.
The fields in the query do not change, just whether there is data or not.
Explination:

table - used to enter data on
query1 - sorts data per week (week 16)
crosstab - sorts query1 per day (sun thru sat of week 16 with totals per day
and per week)
report - displays crosstab

Even though I know that there will always be seven days in the week,
sometimes data is not entered. I want to be able to display the blank column
in the report for the day missing.

I have tried to put an if then function(=if(iserror([mon]),null,[mon])) but
then nothing shows on the report at all even if there is data to display. I
have even tried ismissing, isnull and any others that I can think of.

Is there another way to leave that day blank but still display the report
without erroring?