Sum Total #error in continuous sub-form footer (1 Viewer)

sharq

New member
Local time
Today, 15:13
Joined
Oct 19, 2010
Messages
8
Hi Everyone,
I am not an IT guy, I have designed an access database for inventory and sales details for some analysis and reporting. I have a subform within a form, but the problem is that when I want to calculate the sum total of all values for the rows/ records for the subform in the subform footer, it gives an #error message in the those unbound textboxes.
I have three columns to be summed up, viz., Units, BankedAmt & OtherAmt (through debit/ credit cards) which are bound forms. The number of records in one single entry (main form) could run into three figures (for subform). I have two more columns after these three columns for TotalAmt (Banked Amt – Other Amt) and CostperUnit (TotalAmt / Units), the results of which are displayed correctly on each record in subform.
However, the problem is that I cannot sum total all the units/ BankedAmt and OtherAmt in the subfooter of the subform for confirming the total of each Bank receipt which is my one single transaction for the main form (when I use Sum/Agg).
I’ve even tried a combination of some formulas after checking on the net but just cannot get the result of sum of all units.
I am novice in creating an access database though I have used the applications some years back. Help in this regard would be really appreciated coz I want to display the total for that single transaction for the main as the details are captured in the subform and I want the totals on the subform footer only to know that all the details are captured correctly.

I guess that has nothing to do with the last row which is by default blank and has null values but that is normal I suppose. I use access 2003 SP3.
In anticipation of some early response.


 

Poppa Smurf

Registered User.
Local time
Today, 23:13
Joined
Mar 21, 2008
Messages
448
You will need something like this in the footer of your subform
TotalUnits=nz(sum([Units]))
TotalBankedAmt=nz(sum([BankedAmt]))
TotalOtherAmt=nz(sum([OtherAmt]))

I use the above format to give me the total of the account due, total amount that I have paid, then the third total gives me the balance to be paid.
 

sharq

New member
Local time
Today, 15:13
Joined
Oct 19, 2010
Messages
8
Hi Poppa Smurf,
Appreciate your remedy...
I've tried this formula b4 but no luck. But after I type this formula, the resultant #error is left aligned (any clue).
I have a lot of rows/ records in my subform, it could run in 3 figures sometimes for one single record for the main form, but no functions work in my subform footer, I did try the =[units], just for testing purpose and it gave me the value of the first record for units in my subform.
Why are all the rows/ records for the units field not summed up in my subform footer. I dunno if this is something to do with version 2003, but used to work on similar type of application on Access 2000 some years back with no issues.
 

Poppa Smurf

Registered User.
Local time
Today, 23:13
Joined
Mar 21, 2008
Messages
448
Try =nz(Sum([units])).

Check that units is a Number not Text data type.
 

missinglinq

AWF VIP
Local time
Today, 08:13
Joined
Jun 20, 2003
Messages
6,423
Are these fields you're running Sum() against calculated fields, perchance? You cannot use Aggregate functions against calculated fields.

Simply put, if

A + B = C

you cannot use

Sum(C)

but rather must use

Sum(A + B)

Linq ;0)>
 

sharq

New member
Local time
Today, 15:13
Joined
Oct 19, 2010
Messages
8
Hi Missinglinq,
No, they are not calculated fields but bound forms to a table different from the table for the main form.
Thanks in advance for any solutions.
 

sharq

New member
Local time
Today, 15:13
Joined
Oct 19, 2010
Messages
8
=nz(Sum([Units]))
I've even tried SUM([Units]) which should have worked but....
 

missinglinq

AWF VIP
Local time
Today, 08:13
Joined
Jun 20, 2003
Messages
6,423
And Units, with an s, is the actual name of the Control holding the data, as opposed to the name of the Field bound to the Control?
 

sharq

New member
Local time
Today, 15:13
Joined
Oct 19, 2010
Messages
8
That's right. Both share the same name. What difference could that make. The sum function should have worked, it's simple summing up all the records to be displayed in the subform footer but why is that not happenning.
 

missinglinq

AWF VIP
Local time
Today, 08:13
Joined
Jun 20, 2003
Messages
6,423
Sorry, running out of ideas! The only way I can reproduce the error is if the field and control have different names and I try to Sum using the field name rather than the control name.

Could you zip the db up and attach it in a post here? Also give us the name of the form you're talking about.
 

sharq

New member
Local time
Today, 15:13
Joined
Oct 19, 2010
Messages
8
Hi,
I have attached the db. I had created a db with just two tables y'day, one for main form and the other for the sub-form and used the sum() on the subform subfooter, it worked fine. But then I again recreated my db by copying the tables and recreated the main form and the sub form and tried using the sum formula and there it came, the #error to haunt me once again.
Is there is some procedural mistake I am missing, I did not create the relationships before creating the form which I had done when I had done my original db, simply put I cannot understand why sum formula is failing in this db.
The main form and the sub-form are having fields from two tables, viz., ReceiptHead & ReceiptDetails and are joined by BRHTranNo (RH Table) and SLRTranNo (SR Table). Most of my forms will be combo boxes referenced through queries later but that would be after the sum total problem is solved for my subform.
The subform is SALERECEIPT SUBFORM.

Thanks !

:confused:
 

Attachments

  • dbexpb4R.mdb
    648 KB · Views: 734

Poppa Smurf

Registered User.
Local time
Today, 23:13
Joined
Mar 21, 2008
Messages
448
Sharq

Here is an updated version that now works.

When calculating totals you must total the field names not the text e.g. Total Units must be calculated using =sum([SLRUnits]) not =Sum([Units]). Units is not a field name

I have changed your subform and the calcualtions appear to work. I have changed the alignment of your number fields to Right in lieu of General. General has not specific alignment it could be Left, Right, Centre etc.

Have a look at ReceiptDetails subform1 I changed the record source by creating a field called Units, Baked amount etc. Then created another field Called TotalAmt which does your calculation using the query and you do not have to enter the formual in the text box.

To see this new subform working change the name of your current subform to another name then change ReceiptDetails subform1 to ReceiptDetails subform.
 

Attachments

  • dbexpb4RNew.mdb
    440 KB · Views: 1,684

missinglinq

AWF VIP
Local time
Today, 08:13
Joined
Jun 20, 2003
Messages
6,423
That's right. Both (field and control) share the same name.
So in point of fact this statement was not true. This is not meant to chide, but you have to be accurate in presenting a problem here, just as you do in all programming.

I'm glad Poppa got it fixed for you!

Good luck with your project!

Linq ;0)>
 
Last edited:

Poppa Smurf

Registered User.
Local time
Today, 23:13
Joined
Mar 21, 2008
Messages
448
Glad to assist.

This is also true when totalling in a report you must use the same format. i.e. total the field not the text box.
 

sharq

New member
Local time
Today, 15:13
Joined
Oct 19, 2010
Messages
8
Dear Popup Smurf and Missinglinq,
I have no words but Thanks ! Thanks ! Thanks ! and just Thanks ! and I'll always be very grateful to both of you for taking interest in solving my problem.
I did not anticipate that this problem could be fixed this fast coz I'd thought that there was some problem in my database design, the linking and so on, etc., so now I can start with some serious work on this.
Really appreciate and all the best to you guys and continue with your good work.

Warm Regards,

P.S. - Sorry for responding late.
 

Users who are viewing this thread

Top Bottom