User Function - limit on arguments

[PFW]

I have created a user function that averages values in columns for each
record. It seems to work fine up to a point. The table has 30 columns for
scores. I can pass 29 columns/values to the function but when I pass the
30th column I get a message that says teh expression is too complex.

I can't find anything on limitations of this type. The user function has a
ParamArray as its single argument.

Any thoughts?

 

[Duane Hookom]

Have you considered normalizing your table structure? Normalization is a
great solution for getting rid of complexity.

 

[Rick Brandt]

I have created a user function that averages values in columns for
each record. It seems to work fine up to a point. The table has 30
columns for scores. I can pass 29 columns/values to the function but
when I pass the 30th column I get a message that says teh expression
is too complex.

I can't find anything on limitations of this type. The user function
has a ParamArray as its single argument.

Any thoughts?

Any time you find yourself needing to aggregate across columns it most likely
means that your table design is incorrect. If you had a one table with a
one-to-many relationship to a second table and the second table had 30 ROWS of
records for scores then you could use a simple totals query grouping by the
Foreign Key and using Avg() to get the average.

That aside I don't know of any inherant limit on the number of values that can
be passed with a paramArray. It wouldn't surprise me that there is one, but 29
seems awfully low.

 

[John Spencer]

My guess is that you might be exceeding the length of text allowed in a
"cell" in the query grid. The function should be able to handle that many
items.

I have one I just tested with 40 items passed in the parameter array and it
had no problems.

From MS Access help Specifications
Number of characters in a cell in the query design grid 1,024

 

[David Cox]

perhaps use a collection instead?

Dim mycols As New Collection

 

[PFW]

Not that - total characters in the cell is 402.

My guess is that you might be exceeding the length of text allowed in a
"cell" in the query grid. The function should be able to handle that many
items.

I have one I just tested with 40 items passed in the parameter array and it
had no problems.

From MS Access help Specifications
Number of characters in a cell in the query design grid 1,024

 

[John Spencer]

Can I suggest that you post the procedure?

Also, is it always the 30th column that causes the problem? Or does it work
for columns 1-19, but not for columns 1-19 and 30?

Here is one that I wrote about 6 years ago to handle this problem with
non-normalized data I was getting from an external source. It will handle
at least 40 arguments in the parameter array. Since I didn't type the array
it will handle strings, numbers, and even dates (although the latter doesn't
make a lot of sense).

Public Function fGetMeanAverage(ParamArray Values())
'John Spencer UMBC CHPDM
'Last Update: April 5, 2000
'Calculates the arithmetic average (mean) of a group of values passed to it.
'Sample call: myAvg = GetMeanAverage("1","TEST","2", "3",4,5,6,0) returns 3
(21/7)
'Ignores values that cannot be treated as numbers.

Dim i As Integer, intElementCount As Integer, dblSum As Double
intElementCount = 0
dblSum = 0

For i = LBound(Values) To UBound(Values)
If IsNumeric(Values(i)) Then 'Ignore Non-numeric values
dblSum = dblSum + Values(i)
intElementCount = intElementCount + 1
End If
Next i

If intElementCount > 0 Then
fGetMeanAverage = dblSum / intElementCount 'At least one number in the
group of values
Else
fGetMeanAverage = Null 'No number in the group of values
End If

End Function

 

[Rick Brandt]

Changing the design for the convenience of queries may result in a
significantly more complex design.

Simplifying the example, consider a table that holds Things. The
business rules are:

· each Thing must have exactly one 'minimum height value';
· each Thing must have exactly one 'maximum height value';
· for a given Thing, 'minimum height value' must be less than
'maximum height value'.

I don't buy your example. An attribute of 'minimum height value' or
'maximum height value' sounds like a specification, not a measurement.
Those I agree would be stored in fields, not rows, but I fail to come up
with a scenario where I would need to aggregate two or more different
specifications.

Now, if I were taking "Measurements" that I needed to compare to the
specifications then I would want those in many rows of a single field and I
can easily see where I would need to aggregate them.

 

[Rick Brandt]

If you have a issues buying into my fabricated example, let's try
something more arbitrary.

A Thing must have *exactly* 30 measurements. Modelling each as a
column in the same table makes the constraint easy to write (i.e.
make every column NOT NULL) and INSERT/UPDATE/DELETE operations easy
to use e.g.

CREATE TABLE Things (
thingID INTEGER NOT NULL UNIQUE,
measurement_01 INTEGER NOT NULL,
measurement _02 INTEGER NOT NULL,
measurement _03 INTEGER NOT NULL,
...
meanurement_30 INTEGER NOT NULL
);

If you propose that each measurement should be a row, how would you
write effective constraints?

Jamie.

Frankly I wouldn't worrry about writing "effective constraints" for that
requirement.

There are numerous examples of business rules that cannot be implemented
with constraints. For example "Every Sales Order must have at least one
line-item". Surely you wouldn't advocate that the line-item data be moved
into fields of the parent order so you could enforce that with a constraint.

Constraints are useful and have their place. Enforcing every rule with them
is not achievable.

 

[Rick Brandt]

It was the only requirement I specified: worry about it or you're
fired <vbg>!

As discussed upthread, it could be implemented using a table-level
CHECK constraint.

Yes, but "could" <> "should".

To me an entity "Test" that has one or more "Measurements" associated with
it is best modelled with two tables. A requirement that each parent entity
must have a fixed number of related children does not justify moving that
data into the parent table. That would introduce the desirable ability to
use a constraint, but simultaneously introduces many undesirable aspects
that (IMO) would outweigh the advantage of using a constraint.

 

[PFW]

Here is my function - seems to be similar to yours...

Function MyAverage(ParamArray Vals()) As Variant
Dim i As Integer
Dim nScores As Integer
Dim nCount As Integer

nScores = 0
nCount = 0

For i = LBound(Vals) To UBound(Vals)
If Not IsNull(Vals(i)) Then
nScores = nScores + Vals(i)
nCount = nCount + 1
End If
Next i

If nCount > 0 Then
MyAverage = nScores / nCount
Else
MyAverage = Null
End If
End Function

The function is bein used as follows:

Table has columns as follows-
ID (autonumber)
Col1 .. Col30 (longinteger - no default value)

Some columns have values in them - some do not.

The query lists the ID and the Average - the intention is to pass each of
the Col1 ..Col30 to the function and it returns an average of only those
where a value is entered.

This has been tried on two different WinXP machines - one with XP SP1 &
Office 2003 SP1 - the other with XP SP2 & Office 2003 & latest SP (can't
recall number).

This example was meant to be a simplistic test for a real life situation
where an applicant is required to undergo a specified number of up to 30
standard tests. The function is only meant to return the average of tests
completed so far.

The real life example was failing (the table and query were a lot more
detailed) so I tried to be very simple - without luck.

 

[PFW]

Sorry - I forgot to say - it doesn't seem to matter which combination of
columns is included - it will handle 29 but not 30.

 

[John Spencer]

So, does the function work if you just call it in the immediate (debug)
window. That is

?
MyAverage(1,2,3,4,5,6,7,8,9,10,1,2,3,4,5,6,7,8,9,20,1,2,3,4,5,6,7,8,9,30,1,2,3,4,5,6,7,8,9,40)
- which works on my computer
?
MyAverage(10000,20000,30000,40000,50000,6,7,8,9,10,1,2,3,4,5,6,7,8,9,20,1,2,3,4,5,6,7,8,9,30,1,2,3,4,5,6,7,8,9,40)
- fails with an overflow error

I would suggest that you Dim nScores as LONG or Double instead of integer.
If the sum of the numbers you are sending the function exceed 32K (limit of
integer) then you will get an overflow error.

 

[PFW]

John,

There is no problem with it in the Immediate Window and I have tested it
with up to 40 arguments. The variable types are not at issue as the examples
that I am using involve small values in the range of 1 to 10.

The problem occurrs when the function is used in a query. Did you get a
chance to try that?

 

[John Spencer]

No, but then I don't have a table with that structure.

Built a table and tried your function with it and got an error that the
expression was too complex when I entered the 30th element into the
function. So it looks as if the query can't handle passing that many
arguments.

It looks as if you will have to normalize that data or write some vba that
will take the primary key of the row and build an internal query to get the
results. Something like the UNTESTED AIRCODE below - add error handling and
set objects to nothing as appropriate. (Also, other things you could do to
optimize this).

The best optimization would be a table redesign, but if you can't do that
....

Public Function myFunkyAverage(PKValue)
Dim dbAny As DAO.Database
Dim rstAny As DAO.Recordset
Dim strSQL As String
Dim iLoop As Integer
Dim dblSum As Double
Dim intElementCount As Integer

Set dbAny = DBEngine(0)(0)
strSQL = "Select field1, field2, field3, ... , Field40 FROM table2 where
PK = " & Chr(34) & PKValue & chr(34)
'Drop the Chr(34) if you primary key is a number field

Set rstAny = dbAny.OpenRecordset(strSQL)

For iLoop = 1 To rstAny.Fields.Count - 1
If IsNumeric(rstAny.Fields(iLoop)) Then 'Ignore Non-numeric
values
dblSum = dblSum + rstAny.Fields(iLoop)
intElementCount = intElementCount + 1
End If
Next iLoop

If intElementCount > 0 Then
myFunkyAverage = dblSum / intElementCount
Else
myFunkyAverage = Null 'No number in the group of values
End If

rstAny.close
Set dbAny = Nothing
End Function

 

[PFW]

Thanks John - that works fine although it was less generic than I had hoped
for. But we can't have everything I guess.

Thanks again.
Paul

 

[John Spencer]

Well, as noted earlier, this is a problem because of the table structure.
If the table was more like
--PrimaryKey (This could be the combination of the next two fields and
therefore not needed as standalone field at all)
--ForeignKeyField (Points to original Table's Primary Key)
--ValueName (Name of field currently holding the value)
--Amount (Value currently stored in field)

Then getting the average, Max, min, sum, etc of those fields would be
straight forward in a query.

You could generalize the function a bit more if you table structure was such
that you had a primary key and the fields you were going to total were in
consecutive order. Then you could pass the tablename, primaryFieldName, a
primary key value, a start field position and a stop field position.

The SQL statement would be
"SELECT * FROM [" & strTableName & "] WHERE [" & strPrimaryField & "] = " &
PrimaryKeyValue

And the loop would run

For iLoop = IntStart To IntEnd
...
Next iLoop