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
Aaron GoodwinAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
One other thought -

You *might* need to do this in the Report Footer - not the page footer.

I'm not positive about that, though.  I generally prefer to automate Excel reports, and don't use Access reports often.
0
 
Aaron GoodwinAuthor Commented:
Here are some screenshots minus the managers names.  I also threw in a running sum column just to see if things would add up.....and that works ok.  I'm not sure why I can't get the totals to work in the report

thanks!
Errors.doc
0
 
mbizupCommented:
<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]))"
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
Aaron GoodwinAuthor Commented:
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
0
 
mbizupCommented:
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.
0
 
Aaron GoodwinAuthor Commented:
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
0
 
Aaron GoodwinAuthor Commented:
Thanks for the help, just couldn't see what was going wrong as I was staring at it for so long.  Appreciate it!
0
 
mbizupCommented:
Glad to help out :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.