Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 366
  • Last Modified:

How to perform many additions in access report?

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!
0
Jarred Meyer
Asked:
Jarred Meyer
  • 11
  • 7
  • 4
  • +2
6 Solutions
 
BardobraveCommented:
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.
0
 
Jarred MeyerProduction ManagerAuthor Commented:
I am trying to do math with this. Addition. Sorry for not clarifying that. I just want to sum all of these fields.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Jarred MeyerProduction ManagerAuthor 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
0
 
Jeffrey CoachmanMIS LiasonCommented:
<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
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Try moving the calculation to a different event - perhaps the Format or Print event of the section containing the textboxes.
0
 
Jarred MeyerProduction ManagerAuthor 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?
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
Jeffrey CoachmanMIS LiasonCommented:
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?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
Helen FeddemaCommented:
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.
0
 
Jarred MeyerProduction ManagerAuthor 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.
0
 
Jarred MeyerProduction ManagerAuthor 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!
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
Jarred MeyerProduction ManagerAuthor 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.
0
 
Jeffrey CoachmanMIS LiasonCommented:
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
0
 
Jeffrey CoachmanMIS LiasonCommented:
Something perhaps like this...
Database94.mdb
0
 
Jarred MeyerProduction ManagerAuthor Commented:
Jeff, That makes since. Let me work on that. Thanks for the help!
0
 
Jeffrey CoachmanMIS LiasonCommented:
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...
0
 
Jarred MeyerProduction ManagerAuthor 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..
0
 
Jarred MeyerProduction ManagerAuthor Commented:
Oh my apologies.. I missed Helena.. I'll request attention to correct this. Sorry Helena. I'll split the points up more fairly.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
 
Jarred MeyerProduction ManagerAuthor Commented:
Good to know LSM. Thanks.
0
 
Jarred MeyerProduction ManagerAuthor Commented:
Thanks everyone for your help on this!
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.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 11
  • 7
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now