Sum Problem

[postmanphat]

Hi,

So I've got a form that displays a load of stats about my database that are calculated using simple count queries. The form is based on a query that itself draws figures in from 12 other count queries. No Problem so far - the form displays all 12 counts fine. The problem comes when I simply try to display the total of the 12 fields on the form in a textbox using =sum([field1]+[field2]+[field3]....) It is quite happy to do this fine with up to 10 fields in the formula, but as soon as you add the 11th, it returns an error!? And its not the field because it doesn't matter waht the 11th field is, it just doesn't like trying to add up more than 10 fields.

Any ideas how I might get round this would be most appreciated.

Dave
Oh - I'm using Access 2000

 

[jontout]

Have you tried passing the total to the textbox in VBA?

 

[genomon]

I took a shot at this one too, but in the Access Forms forum.

Always remember that you're unique. Just like everyone else.

 

[MajP]

If there really is a limit to a calculated sum on a forms control then you could write your own function. I never heard there was a limit so not sure if this is your problem

Public Function mySum(ParamArray varVals()) As Double
  Dim varVal As Variant
  For Each varVal In varVals()
    mySum = mySum + Nz(varVal)
  Next varVal
End Function

in a control
=mySum([txtBx1],[txtBx2],...[txtBxn])

The other solution is to write a calculted control in the forms query not in the control.

 

[randy700]

Another (albeit sloppy) solution:
Hidden TextBox1 = sum(field1 + field2 + field3 + field4 + field5 + field6)
Hidden TextBox2 = sum(field7 + field8 + field9 + field10 + field11 + field12)
Displayed TextBox2 = sum(TextBox1 + TextBox2)


Randy

 

[MajP]

http://blogs.msdn.com/access/archive/2006/06/05/618366.aspx

I think the issue might be the limit on a field property:
255 chararcters. So my solution probably does not get you much of anything if you have long text box names. If you do it with a query (assuming bound form) you can then get 1,024 characters I believe.