Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-12-2019, 08:37 AM   #1
b13
Newly Registered User
 
Join Date: Nov 2019
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
b13 is on a distinguished road
Grand Total shows #error

Hello,


This is my first post..so forgive any mistakes.


I have a continuous form which totals four fields in a single record (in an unbound text box)but when I tried to grand total for whole form in the form footer it shows #error. I have tried couple of methods but none of them works.
I really need help on this.


I tried like this:
=Sum(Nz([Price-A1],0)+Nz([Price-A2],0)+Nz([Price-A3],0)+Nz([Price-A4],0))


Price-A1 to A4 are the 4 fields. I have also included images.
Attached Images
File Type: jpg Capture2.JPG (89.2 KB, 11 views)
File Type: jpg Capture.jpg (87.5 KB, 10 views)

b13 is offline   Reply With Quote
Old 11-12-2019, 08:42 AM   #2
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,567
Thanks: 58
Thanked 1,437 Times in 1,418 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Grand Total shows #error

Hi. Welcome to AWF!


Just curious, can you please verify if you do or don't have any null values in any of those fields? Thanks!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-12-2019, 01:30 PM   #3
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,717
Thanks: 105
Thanked 1,511 Times in 1,424 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Grand Total shows #error

Try this:
Code:
=Sum(Nz([Price-A1],0))+Sum(Nz([Price-A2],0))+Sum(Nz([Price-A3],0))+Sum(Nz([Price-A4],0))

Galaxiom is offline   Reply With Quote
Old 11-12-2019, 01:58 PM   #4
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,229
Thanks: 10
Thanked 231 Times in 219 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Grand Total shows #error

If post 3 doesn't work then consider this: AFAIK, you cannot sum over the controls themselves, you must sum over the fields they are bound to. e.g. if your textbox is named txtNum and is bound to myField then you must sum over myField, not txtNum. In your pic, it seems that the fields and controls have the same name - something I do not do for other reasons. Perhaps I can add this one to my list; i.e. the expression is ambiguous as a result.
__________________
Sometimes I just roll my eyes out loud...
Windows 10; Office 365 (Access 2016)
Micron is offline   Reply With Quote
Old 11-12-2019, 02:20 PM   #5
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,717
Thanks: 105
Thanked 1,511 Times in 1,424 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Grand Total shows #error

Quote:
Originally Posted by Micron View Post
In your pic, it seems that the fields and controls have the same name - something I do not do for other reasons. Perhaps I can add this one to my list; i.e. the expression is ambiguous as a result.
AFAIK The Sum() function always refers to fields so there should be no ambiguity.

When an expression could refer to either where fields and controls have the same name, Access defaults to the Controls Collection. I've never bothered to use different names of the controls and never had a problem.

On the very rare occasions where I do need to refer to a field rather than a control I use:

Code:
Me.Recordset.fieldname
It isn't necessary but the complimentary expression can be used for the control if desiring to be explicit:
Code:
Me.Controls.controlname
Galaxiom is offline   Reply With Quote
Old 11-12-2019, 02:31 PM   #6
Micron
AWF VIP
 
Join Date: Oct 2018
Location: Ontario, Canada
Posts: 1,229
Thanks: 10
Thanked 231 Times in 219 Posts
Micron has a spectacular aura about Micron has a spectacular aura about
Re: Grand Total shows #error

In a test form, this works
=Sum([chkyes]+[dsid])

this results in #Error
=Sum([Check32] + [dsid])

I once got the error "Ambiguous name detected" IIRC and identical names was the reason. I realize this is usually a compile error when there are 2 procedures with the same name. Anyway, it was a long time ago. For now, all I can be sure of is what I posted about the 2 examples in this post.

EDIT
the issue with the above seems to be that the control name is Check32, not [Check32]. Access seems insistent on adding the [] around the control name.

Last edited by Micron; 11-12-2019 at 02:41 PM.
Micron is offline   Reply With Quote
Old 11-12-2019, 08:01 PM   #7
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,466
Thanks: 0
Thanked 573 Times in 569 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Grand Total shows #error

OP's original expression works for me even with Detail section textbox and field having same name.

If you want to provide db for analysis, follow instructions at bottom of my post.

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 11-12-2019, 09:46 PM   #8
AccessBlaster
.
 
Join Date: May 2010
Posts: 1,244
Thanks: 29
Thanked 285 Times in 271 Posts
AccessBlaster has a spectacular aura about AccessBlaster has a spectacular aura about
Re: Grand Total shows #error

Sometimes you need to hide a control if you want the results displayed in a particular place.
Just a thought.

Last edited by AccessBlaster; 11-13-2019 at 05:55 AM.
AccessBlaster is offline   Reply With Quote
Old 11-13-2019, 08:11 AM   #9
b13
Newly Registered User
 
Join Date: Nov 2019
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
b13 is on a distinguished road
Re: Grand Total shows #error

Quote:
Originally Posted by theDBguy View Post
Hi. Welcome to AWF!


Just curious, can you please verify if you do or don't have any null values in any of those fields? Thanks!

I have some null values in those fields.
b13 is offline   Reply With Quote
Old 11-13-2019, 08:11 AM   #10
b13
Newly Registered User
 
Join Date: Nov 2019
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
b13 is on a distinguished road
Re: Grand Total shows #error

Quote:
Originally Posted by Galaxiom View Post
Try this:
Code:
=Sum(Nz([Price-A1],0))+Sum(Nz([Price-A2],0))+Sum(Nz([Price-A3],0))+Sum(Nz([Price-A4],0))

I have tried it but doesn't work.
b13 is offline   Reply With Quote
Old 11-13-2019, 08:13 AM   #11
theDBguy
I’m here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,567
Thanks: 58
Thanked 1,437 Times in 1,418 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Grand Total shows #error

Quote:
Originally Posted by b13 View Post
I have some null values in those fields.
Hi. Thanks for responding. Just for fun, try entering a value, such as 0, into those null fields, so that you don't have any more null fields and see if the total shows up. You can try this out on a test copy of your db first.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-13-2019, 01:09 PM   #12
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,717
Thanks: 105
Thanked 1,511 Times in 1,424 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Grand Total shows #error

Quote:
Originally Posted by b13 View Post
I have tried it but doesn't work.
You need to post a small sample database so we can see what you actually have.

Novices almost invariably confuse "fields" (in the recordset) and "controls" (textboxes etc) in their questions and in this case that definitely makes understanding the problem difficult.

The crux of it is what Micron said. The Sum() can only be applied to fields in the recordset. It cannot apply to controls on the form.

Galaxiom is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
removing Total and Grand Total from pivot table BRayVT Forms 2 10-06-2017 05:52 AM
[SOLVED] Line Total shows #Error on form when trying to show concatenation DarrellC Forms 1 07-10-2017 08:15 PM
Grand-total from sub-total in a report which has been grouped? rasghar12 Reports 4 07-10-2011 10:54 AM
Totals, sub-total and grand total wont desplayed from Access report to Excel. amunafc Reports 4 12-19-2009 01:36 AM
Error in Sum Grand Total Field Lister Reports 9 03-23-2004 09:08 AM




All times are GMT -8. The time now is 04:02 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World