|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
December 21st, 2009, 02:15 AM
posted to microsoft.public.access.reports
|
|||
|
|||
Crosstab report - handling Null fields
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. 
|
|
#2
December 21st, 2009, 06:35 AM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Crosstab report - handling Null fields
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? -- Duane Hookom Microsoft Access MVP "NEWER USER" wrote: 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.
|
|
#3
December 21st, 2009, 01:47 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Crosstab report - handling Null fields
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? "Duane Hookom" wrote: 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? -- Duane Hookom Microsoft Access MVP "NEWER USER" wrote: 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.
|
|
#4
December 21st, 2009, 03:30 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Crosstab report - handling Null fields
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"); -- Duane Hookom Microsoft Access MVP "NEWER USER" wrote: 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? "Duane Hookom" wrote: 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? -- Duane Hookom Microsoft Access MVP "NEWER USER" wrote: 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.
|
|
#5
December 21st, 2009, 04:16 PM
posted to microsoft.public.access.reports
|
|||
|
|||
|
Crosstab report - handling Null fields
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! "Duane Hookom" wrote: 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"); -- Duane Hookom Microsoft Access MVP "NEWER USER" wrote: 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? "Duane Hookom" wrote: 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? -- Duane Hookom Microsoft Access MVP "NEWER USER" wrote: 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.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
