Access Report Sum Error from Crosstab Query
Hi Experts,
I am having problems adding up totals in my report colums. What I have is a query that does some basic selects, then a crosstab query groups the data I need for me and counts things up nicely. I can then use the crosstab query as the control source for the report. My data isin't very complex:
Manager name Station Compliant Non Compliant
so the manger names are grouped together so the numeric values in Compliant and Non Compliant are grouped counts by the manager name. On my report in the Page footer or in the report footer I would like to sum the compliant and the Non Compliant over all. Not by the manager, but just add up everything in the column.
I can get all the information to display correctly except the totals where I get #Error on the report. I thought it was possibly a spelling mistake (in my page footer I have a text box with "=sum(Nz(Compliant))") but that was not the case. The text box that I want to sum up is called Compliant in the detail header. There are some null values in the column so I thought I needed the Nz. I also tried to sum the control source value which is called "0" zero
Not sure what I am doing wrong, I am new to access and report building
I am having problems adding up totals in my report colums. What I have is a query that does some basic selects, then a crosstab query groups the data I need for me and counts things up nicely. I can then use the crosstab query as the control source for the report. My data isin't very complex:
Manager name Station Compliant Non Compliant
so the manger names are grouped together so the numeric values in Compliant and Non Compliant are grouped counts by the manager name. On my report in the Page footer or in the report footer I would like to sum the compliant and the Non Compliant over all. Not by the manager, but just add up everything in the column.
I can get all the information to display correctly except the totals where I get #Error on the report. I thought it was possibly a spelling mistake (in my page footer I have a text box with "=sum(Nz(Compliant))") but that was not the case. The text box that I want to sum up is called Compliant in the detail header. There are some null values in the column so I thought I needed the Nz. I also tried to sum the control source value which is called "0" zero
Not sure what I am doing wrong, I am new to access and report building
<The text box that I want to sum up is called Compliant in the detail header>
Make sure you are using the name of the underlying filed (not a control name) in your sum statement.
Using the name of a control will definitely give you an error.
Also, if your field name contains spaces or other special characters, use brackets.
In other words:
"=sum(Nz([Some Field Name From the Report's Recordsource]))"
Make sure you are using the name of the underlying filed (not a control name) in your sum statement.
Using the name of a control will definitely give you an error.
Also, if your field name contains spaces or other special characters, use brackets.
In other words:
"=sum(Nz([Some Field Name From the Report's Recordsource]))"
ASKER
Thanks for the idea, but I Tried that too
In other words:
"=sum(Nz([Some Field Name From the Report's Recordsource]))"
in my case the record source is actually called "0" zero
"=sum(Nz([0]))"
still returns an #error
In other words:
"=sum(Nz([Some Field Name From the Report's Recordsource]))"
in my case the record source is actually called "0" zero
"=sum(Nz([0]))"
still returns an #error
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
And finally - just for yucks -
If that fails, try the same thing using a different field name. [0] and [1] are unconventional field names, and that could be messing things up.
If that fails, try the same thing using a different field name. [0] and [1] are unconventional field names, and that could be messing things up.
ASKER
There ya go. Thank you very much. I had tried something similar with no luck...I think I was using the textbox values. And not the control source values. You rock
ASKER
Thanks for the help, just couldn't see what was going wrong as I was staring at it for so long. Appreciate it!
Glad to help out :-)
ASKER
thanks!
Errors.doc