Solved

How to perform many additions in access report?

Posted on 2012-03-12
25
341 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!
0
Comment
Question by:SeyerIT
  • 11
  • 7
  • 4
  • +2
25 Comments
 
LVL 19

Expert Comment

by:Bardobrave
Comment Utility
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
 
LVL 2

Author Comment

by:SeyerIT
Comment Utility
I am trying to do math with this. Addition. Sorry for not clarifying that. I just want to sum all of these fields.
0
 
LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 84 total points
Comment Utility
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
 
LVL 2

Author Comment

by:SeyerIT
Comment Utility
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
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 333 total points
Comment Utility
<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
 
LVL 84
Comment Utility
Try moving the calculation to a different event - perhaps the Format or Print event of the section containing the textboxes.
0
 
LVL 2

Author Comment

by:SeyerIT
Comment Utility
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
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 333 total points
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 
LVL 84
Comment Utility
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
 
LVL 31

Assisted Solution

by:Helen_Feddema
Helen_Feddema earned 83 total points
Comment Utility
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
 
LVL 2

Author Comment

by:SeyerIT
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 2

Author Comment

by:SeyerIT
Comment Utility
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
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 333 total points
Comment Utility
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
 
LVL 2

Author Comment

by:SeyerIT
Comment Utility
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
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 333 total points
Comment Utility
Something perhaps like this...
Database94.mdb
0
 
LVL 2

Author Comment

by:SeyerIT
Comment Utility
Jeff, That makes since. Let me work on that. Thanks for the help!
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
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
 
LVL 2

Author Comment

by:SeyerIT
Comment Utility
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
 
LVL 2

Author Comment

by:SeyerIT
Comment Utility
Oh my apologies.. I missed Helena.. I'll request attention to correct this. Sorry Helena. I'll split the points up more fairly.
0
 
LVL 84
Comment Utility
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
 
LVL 2

Author Comment

by:SeyerIT
Comment Utility
Good to know LSM. Thanks.
0
 
LVL 2

Author Closing Comment

by:SeyerIT
Comment Utility
Thanks everyone for your help on this!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now