How to perform many additions in access report?

Vba will try to add the values you are passing, as "+" operator is not able to concat strings. So, if you want to concat the strings of the texts you should use the "&" operator instead of "+", and if you actually want to add the values, you'd probably convert some of them depending on the values they contains.

I am trying to do math with this. Addition. Sorry for not clarifying that. I just want to sum all of these fields.

Hey LSM, It's possible putting these calculations in the query could be better. The query is pretty heavy in the calculations as is. It was just easier to put these calculations together on the report. There are lots of different calculations/sums, etc. going on in the report and I didn't think I could do all these seperate calculations in the query.

I did try;
Me.txtTotMfgExp = Nz(Me.txtUtilities, 0) + Nz(Me.txtProperty, 0) + Nz(Me.txtRent, 0) + Nz(Me.txtDepreciation, 0) + Nz(Me.txtPropTax, 0)

in the OnOpen event of the report though and still got an error.

Try moving the calculation to a different event - perhaps the Format or Print event of the section containing the textboxes.

Ok.. Here are the queries in order;

qryCSLPLBalanceSheet:
SELECT tblPerTB.DateEOM, tblPerTB.GLAccount, tblPerTB.GLDesc, tblPerTB.Amount
FROM tblPerTB
WHERE (((tblPerTB.DateEOM)=Eval('[Forms]![frmCSLPL]![txtEndDate]')));

qryCSLPLwoLbr:
SELECT Seq, Category, LineItem, Sum(Amount) AS SumOfAmount, ReportLocation, Variable
FROM qryCSLPLBalanceSheet INNER JOIN tblCriteria ON (qryCSLPLBalanceSheet.GLAccount <= tblCriteria.AccountEnd) AND (qryCSLPLBalanceSheet.GLAccount >= tblCriteria.AccountStart)
GROUP BY tblCriteria.Seq, tblCriteria.Category, tblCriteria.LineItem, tblCriteria.ReportLocation, tblCriteria.Variable
HAVING (((tblCriteria.ReportLocation)="CSLPL"));


qryCSLPL:
SELECT qryCSLPLwoLbr.Seq, qryCSLPLwoLbr.Category, qryCSLPLwoLbr.LineItem, qryCSLPLwoLbr.SumOfAmount * qryCSLPLwoLbr.Variable AS SumOfAmount
FROM qryCSLPLwoLbr;

UNION

SELECT 9 AS Seq, "LaborHours" AS Category, "Labor Hours" AS LineItem, Sum([ldLaborHrs]) AS SumofAmount
FROM qryCSLPLLbrHrs;

UNION

SELECT 10 AS Seq, "LaborHours" AS Category, "Earned Hours" AS LineItem, Sum([ldEarnedHrs]) AS SumofAmount
FROM qryCSLPLLbrHrs;

UNION

SELECT 11 AS Seq, "LaborHours" AS Category, "Direct Labor $ Incurred" AS LineItem, Sum([ldAmount]) AS SumofAmount
FROM qryCSLPLLbrHrsDirect;

UNION

SELECT 12 AS Seq, "LaborHours" AS Category, "Total Labor $ Incurred" AS LineItem, Sum([ldAmount]) AS SumofAmount
FROM qryCSLPLLbrHrs;


(I didn't include the labor hours query since I don't think it was necessary for this question.. Let me know if you need that as well)

Would it make more since for me to create this sums in the last Union query some how?

Here is the the formula I tried to place in the textbox in the report footer;

txtUtilities + txtProperty + txtRent + txtDepreciation +

txtPropTax + txtMfgSup + txtQualAudMan + txtTools +

txtEquipRep + txtVehExp + txtBuildRepMtc + txtTrainEduc +

txtContImpr + txtEmplPreInj + txtWasteDispTrsh + txtCompExpSup + txtSafeFirAid +

txtSecSys + txtTelT1CdlsExp + txtPostage + txtFrghtDelry

+ txtOffSupp + txtCopPrint + txtPayExp + txtUniforms +

txtShopExp + txtTrvlMls + txtDuesSubs + txtEmpRec +

txtSusp

Somehow or another this worked this time when I did this. When I tried this Friday, it gave me an error that said the string was too long to be calculated or something. Now it's working.

Anyways, I'll still take any advice you have on a better method to calculate this.. In the union query maybe?

Perhaps LSM can spend more time digging into this, ...as for me, ...a *Sample* db is always best.

To be honest, I am a bit more confused.
You asked about one report, now you stated:
<Here are the queries in order;>
Queries?
How many queries does this one report have?

IMO, when you must resort to multiple UNION queries, you are often better off building a temporary table and using that for your report. Also, in many cases the overuse of UNION queries often indicate a problem with the database structure - of course, you may not really have much/any control over that, but it's worth mentioning.

Also, in many cases the overuse of UNION queries often indicate a problem with the database structure - of course, you may not really have much/any control over that, but it's worth mentioning.

Chances are, it's because of my lack of knowing the best way to gather this information.. This is just what makes since to me as I don't have a substantial amount of experience with Access.

Alright.. I'm pausing on my current method right now so I can explain what exactly it is I'm trying to accomplish. What you all know so far is that I'm trying to do a bunch of calculations and sums in a Report footer.. At this point I have hit my section length maximum of 22".

So I need to find a way to get some of my information and calculations into the detail section by performing some calculations at the query. That is what I most will need help determining the best way to do this.

I'm thinking the best way for you to see what I'm trying to do is to see the format of the source data? I'm including an excel document which shows all of the lines of data. I set the values to zero.

I have many different lines of data in that table that I need to format into a report. Many of the items will be totaled together and then those totals used in different parts of the report. Using the report footer seemed to give me the flexibility I was looking for under the circumstances. But there could be an easy way to do this in a query?

Here is the source data
Experts.xlsx

Here is the format of the report so far..

Maybe this can give you an outline of what I'm trying to accomplish with the source data? Most of the lines in the report are straight form the Source Data. Problem I've run into utilizing the Detail section of the report is there are several Sums, etc. that are a compilation of different lines of that data.

Let me know what you think. Or what other information you may need.

Thanks very much for your help!

Jeff, I was thinking that may be a good option. Just wanted to see what you guys thought.. At least now that you have somewhat of a broader picture.

Yes, but to give you some more insight.

You may think that "performance" may be an issue here...

Remember P&Ls are rarely viewed on a "Hourly" basis, perhaps not even daily...

So even if it takes your system while to chug through all the calculations, it may not need to be "refreshed" for a while.
So you can put your entire "consolidation/MakeTable" code (or call to this code) possibly on the open event of the report, and simply put up with the delay.
...or you can set up any number of elaborate "Refresh" systems (but this is a separate issue)

JeffCoachman

Jeff, That makes since. Let me work on that. Thanks for the help!

Actually, my post was just on the design of the report.
The other Experts suggested the "Make Table" stuff.
(Which my post would not really work without)

So you should have really split the points...

I would have split the points but was told that when I assign more than one set of points to a particular person Experts was only recognizing the first set of points and the rest were getting dropped off. Apparently a glitch..

Oh my apologies.. I missed Helena.. I'll request attention to correct this. Sorry Helena. I'll split the points up more fairly.

was told that when I assign more than one set of points to a particular person Experts was only recognizing the first set of points and the rest were getting dropped off.

That was an issue when the new EE first rolled out, but it's been corrected. You should award points as you see fit, and if they don't seem to be totaling correctly the Mods/Admins will get is straight.

Good to know LSM. Thanks.

Thanks everyone for your help on this!