Function limits

[monkeytunes]

Hey buddays,

I have a form with a bunch of Count totals in the footer.

There are 12 different areas with a number of projects in each area. So, I'm counting the total projects, and then I'm counting the instances of an area in the area column...12 times. In the query, I have 12 different Count fields, and in the footer of the form, I have Sum([Count]) for each area and one normal Count field for Total Projects. It works fine, until I have about 11 or more text boxes summing counts. Then I get #Error for all of them.

What's the magic Access limit for form-based functions like this? What's a better/faster/sleeker way of doing this?

 

[monkeytunes]

bump

bumpy bump bump

 

[pbaldy]

Not sure if it's relevant, but I helped someone recently where the Sum() function wasn't working in a form footer. It turned out that they had one control that had a source of:

=Sum(TextBoxName)

which you can't have (you can only sum data fields, not controls). That control caused all the other controls with Sum() functions to return #Error. Deleting or fixing that one caused all the others to work properly. Is that the case here?

In a similar vein, make sure your textboxes have different names than their data sources.

 

[monkeytunes]

No, I have all the controls named correctly.

What happens is, if I put 11 or more =Sum([count field from query]) text box controls in the form footer, they ALL turn into #Error. It doesn't matter which ones I keep or delete, if I put more than 11, they all choke.

So, back to my original question - is there another way I should be doing this? A way around this? So on, and so forth.

 

[pbaldy]

Can you post a sample db? I just created a continuous form with sum formulas in the footer, and had 14 with no problem (I quit when I got over 11). For the record, I've got A2k here. I'm not aware of a specific limit on controls with sum formulas, so my guess is that something else is going on.

 

[monkeytunes]

Unfortunately I can't post a sample DB - the backend is using a mix of local tables, linked Access tables, ODBC SQL tables and ODBC Oracle tables. It would take more time than I'm willing to commit to make local Access-version copies of all the tables and upload it.

I'm going to just have to assume that, with these disparate record sets from diffrerent ODBC sources, it's some limit within Access that I've reached. Like I said - 11 SUM controls works fine, but if I try any more it chokes.

I am curious from speed/efficiency standpoints, if there's a better way to do this. I'm using a ton of Count fields in my query to isolate the different criteria, like so:

CountOfCritera1: Sum((IIf([FieldInQuestion]="criteria1",1,0)))
CountOfCriteria2: Sum((IIf([FieldInQuestion]="criteria2",1,0)))
etc.
CountOfCriteria12: Sum((IIf([FieldInQuestion]="criteria12",1,0)))

...and then summing those 12 Counts (plus one Grand Total sum) in the form footer. Is that the best way to do this?

Maybe I need a new thread for that question...

 

[RV]

Just a wild guess...
Could your problem not be caused by exceeding the maximum number of ORACLE and / or SQL connections?

RV

 

[monkeytunes]

I don't know, but I wouldn't think so. Wouldn't all the calculating - i.e. counting and sums - be done on the Access/client side? I have an ODBC record set from which I'm drawing, and my Access query is using the Sum(((IIf[]="",1,0))) fields, and then the form is summing those. It's the calculated fields that are coming up blank, not the queried records.

Someone like Pat or Doc Man could probably answer such a question...::hint hint::

 

[RV]

Wouldn't all the calculating - i.e. counting and sums - be done on the Access/client side?

Yep, but you do require a connection as apparently not all data resides on your Access applic.
So, no connection, no calculation.

It's the calculated fields that are coming up blank, not the queried records.

Sorry, missed that one.
Forget me reply, it's sheer nonsense.

RV