We help IT Professionals succeed at work.

How to perform many additions in access report?

Jarred Meyer
Jarred Meyer asked
on
Medium Priority
378 Views
Last Modified: 2012-03-14
Just trying to add numberous fields in an access report footer. Can't accomplish this in textbox itself as the string for the addition is too long. Anyone have any ideas how I could accomplish this in VBA?

I tried the following on the OnOpen event of the report.

Private Sub Report_Open(Cancel As Integer)

Me.txtTotMfgExp = Me.txtUtilities + Me.txtProperty + Me.txtRent + Me.txtDepreciation + Me.txtPropTax + _
Me.txtMfgSup + Me.txtQualAudMan + Me.txtTools + Me.txtEquipRep + Me.txtVehExp + Me.txtBuildRepMtc + Me.txtTrainEduc + _
Me.txtContImpr + Me.txtEmplPreInj + Me.txtWasteDispTrsh + Me.txtCompExpSup + Me.txtSafeFirAid + Me.txtSecSys + _
Me.txtTelT1CdlsExp + Me.txtPostage + Me.txtFrghtDelry + Me.txtOffSupp + Me.txtCopPrint + Me.txtPayExp + _
Me.txtUniforms + Me.txtShopExp + Me.txtTrvlMls + Me.txtDuesSubs + Me.txtEmpRec + Me.txtSusp


End Sub

Open in new window


When I run that though, I get the following error;

Run-time error '2427':

You entered an expression that has no value.


Thanks in advance for the help!
Comment
Watch Question

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.
Jarred MeyerProduction Manager

Author

Commented:
I am trying to do math with this. Addition. Sorry for not clarifying that. I just want to sum all of these fields.
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
Are any of those values Null? If so, you'll have to use the Nz function for them.

However - would it not be better to include that calculation in the query driving the report? Doing this makes sense in some cases.
Jarred MeyerProduction Manager

Author

Commented:
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.
error
MIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012
Commented:
<Can't accomplish this in textbox itself as the string for the addition is too long.>
I have calculations much longer that that in some of my reports, can you be more specific on what is, or is not happening when you say:
<Can't accomplish this in textbox itself as the string for the addition is too long.> ?

In any event, ...
The Open event may not be the best event to gather control/field values.
For the most part, the Open event occurs before any records are processed.

To access most field/control values, you need code on a "Format" event of that section.

Like LSM, I try to do as many calculations as I can in the Recordsource.
(Among other reasons) This way I can examine the recordsource output directly, (and verify the accuracy), before I run it through a Report and have to wonder about things like Sorting, grouping, Filtering, Formatting, Events, Report View, Print Preview... et al.

Again, I see no reason why a simple calculation like:
Me.txtTotMfgExp = Nz(Me.txtUtilities, 0) + Nz(Me.txtProperty, 0) + Nz(Me.txtRent, 0) + Nz(Me.txtDepreciation, 0) + Nz(Me.txtPropTax, 0)
...could not be done in the recordsource.

Obviously I don't know what:
"The query is pretty heavy in the calculations as is"
...means here in this context, because I don't have access to a sample database...So I can't really comment.

JeffCoachman
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
Try moving the calculation to a different event - perhaps the Format or Print event of the section containing the textboxes.
Jarred MeyerProduction Manager

Author

Commented:
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?
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012
Commented:
Put another way, ...if you want to take a shot at doing the calcs in VBA you need to do it on the Format event of the section the controls are in/on:
 

Sub SomeSection_Format(Cancel As Integer, FormatCount As Integer)
    Me.txtTotMfgExp = Nz(Me.txtUtilities, 0) + Nz(Me.txtProperty, 0) + Nz(Me.txtRent, 0) + Nz(Me.txtDepreciation, 0) + Nz(Me.txtPropTax, 0)
End Sub

But I would try it in the query *first*, this way you don't have to worry about events.
Then you can just bring this field into the report along with all other fields...

JeffCoachman
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
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?
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.
CERTIFIED EXPERT
Top Expert 2009
Commented:
As others have said, it is better to do complex calculations in the record source query, rather than in a control source for a report control.  If the query is too complex, you can run a make-table query to create a table for use as the report's record source.  I have had to do this on occasion, when a report needed many complex calculations based on multiple tables.
Jarred MeyerProduction Manager

Author

Commented:
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.
Jarred MeyerProduction Manager

Author

Commented:
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..
report
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!
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012
Commented:
This is why all of this info is best presented up-fornt...

In an "overall" sense, here is what I an envisioning:
(I may be off here, though...)

Create a query (or temp table or whatever...)  that has each "Calculation" on its own "Record".
This means that each "Record" will be in its own row.
This means that you could put this one set of fields in the detail section.
Hence, the "detail section" need only be as tall as one control.

Then create a "group" field for each of the places where you want a subtotal.
Then simply create a Grouped report with the report wizard. (adding your Groupings and summaries as needed)

JeffCoachman
Jarred MeyerProduction Manager

Author

Commented:
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.
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
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
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012
Commented:
Something perhaps like this...
Database94.mdb
Jarred MeyerProduction Manager

Author

Commented:
Jeff, That makes since. Let me work on that. Thanks for the help!
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
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...
Jarred MeyerProduction Manager

Author

Commented:
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..
Jarred MeyerProduction Manager

Author

Commented:
Oh my apologies.. I missed Helena.. I'll request attention to correct this. Sorry Helena. I'll split the points up more fairly.
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
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.
Jarred MeyerProduction Manager

Author

Commented:
Good to know LSM. Thanks.
Jarred MeyerProduction Manager

Author

Commented:
Thanks everyone for your help on this!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.