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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

mbizupCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.