Grand Total shows #error (1 Viewer)

b13

New member
Local time
Today, 23:09
Joined
Nov 11, 2019
Messages
3
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.
 

Attachments

  • Capture2.JPG
    Capture2.JPG
    89.2 KB · Views: 96
  • Capture.jpg
    Capture.jpg
    87.5 KB · Views: 103

theDBguy

I’m here to help
Staff member
Local time
Today, 09:09
Joined
Oct 29, 2018
Messages
21,358
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!
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 03:09
Joined
Jan 20, 2009
Messages
12,849
Try this:
Code:
=Sum(Nz([Price-A1],0))+Sum(Nz([Price-A2],0))+Sum(Nz([Price-A3],0))+Sum(Nz([Price-A4],0))
 

Micron

AWF VIP
Local time
Today, 12:09
Joined
Oct 20, 2018
Messages
3,476
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.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 03:09
Joined
Jan 20, 2009
Messages
12,849
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
 

Micron

AWF VIP
Local time
Today, 12:09
Joined
Oct 20, 2018
Messages
3,476
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:

June7

AWF VIP
Local time
Today, 08:09
Joined
Mar 9, 2014
Messages
5,423
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.
 

AccessBlaster

Registered User.
Local time
Today, 09:09
Joined
May 22, 2010
Messages
5,823
Sometimes you need to hide a control if you want the results displayed in a particular place.
Just a thought.
 
Last edited:

b13

New member
Local time
Today, 23:09
Joined
Nov 11, 2019
Messages
3
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.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 09:09
Joined
Oct 29, 2018
Messages
21,358
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.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 03:09
Joined
Jan 20, 2009
Messages
12,849
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.
 

Users who are viewing this thread

Top Bottom