Crosstab report - handling Null fields

N

NEWER USER

I have a report that is based on a Crosstab query with three column headings
.. The results of the crosstab query are based on selected rows from a multi
list box. If less than three selections are made OR if three selections are
made and one column contains No Values, the report fails and does not open.
I get the message:
"The Microsoft Jet database engine does not recognize " as a valid
field name or expression." I open the report in design view and see the
error message on the field that does not exist. There are over 2500 rows in
my List Box and no way of knowing which contain/do not contain related
records.

How might I go about hiding the column heading/field when null values arise
as the report opens? Any help appreciated.
 
D

Duane Hookom

It makes the most sense to continue posting in the same thread rather than
creating a new one. Did you implement the solution I suggested earlier that
creates column aliases?
 
N

NEWER USER

YES I did. I created the alias table with sub report headings which
generated the correct column names and the correct data in fileds A,B,C in
Detail section. As long as there is one row row of data in each column, the
report runs. If I select only one row from my list box, the report errors as
Column B and C on the report can't be found. Select two rows with known data
and C can't be found.

I somwhow need to re-create the report each time I run and only show Columns
ABC when data is present from query. Any thoughts from here?
 
D

Duane Hookom

Apparently you might not have set the Column Headings property of the
Crosstab Query. If you look at the SQL of the sample crosstab, you will
notice:

PIVOT tblEmpCustAlias.ColumnAlias In ("A","B","C","D","E","F","G","H");
 
N

NEWER USER

I overlooked that small/HUGE piece of code. Worked perfectly after entering
ABC in Column Headings Properties. Thanks again for all the help. Happy
Holidays!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top