MS Access 2010 Custom Function Argument Limit

[mrleeg]

Greetings,

I would like to know if there is a limit on how many arguments I can have in a custom function to be used in an Access 2010 Query. I have done some experimenting (see code below) and it seems like there is an limit of 29 in Access 2010. If this is true, is there any way to get around this limitation?

The following code works in both MS Excel 2010 and MS Access 2010:

Function Test29(A As Double, B As Double, C As Double, D As Double, E As Double, F As Double, G As Double, H As Double, I As Double, J As Double, K As Double, L As Double, M As Double, N As Double, O As Double, P As Double, Q As Double, R As Double, S As Double, T As Double, U As Double, V As Double, W As Double, X As Double, Y As Double, Z As Double, AA As Double, AB As Double, AC As Double)

Test29 = A + B + C + D + E + F + G + H + I + J + K + L + M + N + O + P + Q + R + S + T + U + V + W + X + Y + Z + AA + AB + AC

End Function​

This code works in MS Excel 2010 but gets a “The expression you entered is too complex.” error in Access 2010:

Function Test30(A As Double, B As Double, C As Double, D As Double, E As Double, F As Double, G As Double, H As Double, I As Double, J As Double, K As Double, L As Double, M As Double, N As Double, O As Double, P As Double, Q As Double, R As Double, S As Double, T As Double, U As Double, V As Double, W As Double, X As Double, Y As Double, Z As Double, AA As Double, AB As Double, AC As Double, AD As Double)

Test30 = A + B + C + D + E + F + G + H + I + J + K + L + M + N + O + P + Q + R + S + T + U + V + W + X + Y + Z + AA + AB + AC + AD

End Function​

Thank you for your assistance.

 

[MarkK]

Whenever I get to a stage like that, where I have something like 29 iterations of data in the same form, then I always want to handle that by constructing some other data structure that it is more efficient or readable, to work with. So if these are values from a row in a worksheet, for instance, pass in reference to the worksheet, and a row number, and upper and lower column limits, so ...

Public Function Test123(wrk as Excel.Worksheet, row as integer, colStart as string, colEnd as string) As Double
[COLOR="Green"]   'this function sums the cells in the given worksheet row from colStart to colEnd[/COLOR]
[COLOR="Green"]   'your code here[/COLOR]
End Function

 

[boblarson]

Yep, 29 appears to be the stopping point. Lagbolt's fix is what I think I would use.

Now it won't work for this situation but for others when you have a multitude of parameters, you don't need to explicitly write them all out. You can use a Parameter Array.

Function Test29(ParamArray  arrInput() As Variant) As Double
   Dim lngCount As Long
   Dim dblAmount As Double
 
   For lngCount = 0 To UBound(arrInput)
          dblAmount = Nz(arrInput(lngCount), 0)
   Next
   Test29 = dblAmount
End Function

Then you can call the same function by using

Test29(F1,F2,F3)
or
Test29(F3,F4,F5,F6,F7,F8,F9)

 

[Pat Hartman]

I'm sure you don't want to hear this but the problem is probably your table design. It feels like a spreadsheet rather than a table. If you properly normalize the schema, you won't have to create custom functions, you will be able to use simple queries to sum the data.

Select Sum(somefield) As YourSum
From YourTable;

 

[boblarson]

It feels like a spreadsheet

Yep, It sure sounded to me like they are using a linked spreadsheet.

For me, I have always set it up to import spreadsheet data unless it is just a quick one-time thing which I need something from the spreadsheet and then I link, get it and delete the link. I don't like using any linked files on a recurring basis as there is just too much that can go wrong and it also forces bad data habits.

 

[tranchemontaigne]

Just out of curiosity, would a rewrite of the function to receive an array as the only argument resolve the original question where a function fails when it receives more than 29 arguments? An array could easily hold more than 29 values. I realize that this question is a step removed from the pragmatic responses that have already been provided.

The function could then call the individual elements contained within the array instead of calling the same values as individual arguments.


(NOTE: I have not been able to get my hands on a copy of MS Access 2010 so I cannot investigate this question directly.)

 

[boblarson]

Just out of curiosity, would a rewrite of the function to receive an array as the only argument resolve the original question where a function fails when it receives more than 29 arguments? An array could easily hold more than 29 values.

Don't know how you would create an array of arguments to be used in the query to call the function.

 

[tranchemontaigne]

Bob,

I was thinking of something more like this.

(1) define function

public function fnAddNumbers(varArray as double) as double
 
[SIZE=1]fnAddNumbers= varArray(0) + varArray(1) +varArray(2) + varArray(3) + varArray(4) + varArray(5) +varArray(6) + varArray(7) + varArray(8) + varArray(9) +varArray(10) + varArray(11) + varArray(12) + varArray(13) +varArray(14) + varArray(15) +varArray(16) + varArray(17) +varArray(18) + varArray(19) + varArray(20) + varArray(21) +varArray(22) + varArray(23) + varArray(24) + varArray(25) +varArray(26) + varArray(27) + varArray(28) + varArray(29)[/SIZE]
 
end function

(2) create array and call function

[I][SIZE=1]dim A, B, C, D, E, F, G, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z, AA, AB, AC, AD as double[/SIZE][/I]
dim arrArgumentArray(0 to 29) as double
 
'SET VALUES
A = 1
B = 1
C = 1
D = 1
E = 1
F = 1
G = 1
H = 1
I = 1
J = 1
K = 1
L = 1
M = 1
N = 1
O = 1
P = 1
Q = 1
R = 1
S = 1
T = 1
U = 1
V = 1
W = 1
X= 1
Y = 1
Z = 1
AA = 1
AB = 1
AC = 1
AD = 1
 
'POPULATE ARRAY
arrArgumentArray(0) = A
arrArgumentArray(1) = B
arrArgumentArray(2) = C
arrArgumentArray(3) = D
arrArgumentArray(4) = E
arrArgumentArray(5) = F
arrArgumentArray(6) = G
arrArgumentArray(7) = H
arrArgumentArray(8) = I
arrArgumentArray(9) = J
arrArgumentArray(10) = K
arrArgumentArray(11) = L
arrArgumentArray(12) = M
arrArgumentArray(13) = N
arrArgumentArray(14) = O
arrArgumentArray(15) = P
arrArgumentArray(16) = Q
arrArgumentArray(17) = R
arrArgumentArray(18) = S
arrArgumentArray(19) = T
arrArgumentArray(20) = U
arrArgumentArray(21) = V
arrArgumentArray(22) = W
arrArgumentArray(23) = X
arrArgumentArray(24) = Y
arrArgumentArray(25) = Z
arrArgumentArray(26) = AA
arrArgumentArray(27) = AB
arrArgumentArray(28) = AC
arrArgumentArray(29) = AD
 
'PASS ARRAY TO A FUNCTION 
call fnAddNumbers(arrArgumentArray())

Though I have never had need to pass such a long list of arguments to a function, I suspect that this approach would work. I m,ake no claims as to the approach being efficient, just that I suspect it would work.

 

[boblarson]

Still doesn't really get at how you get the values from the Spreadsheet (which is where those are) to the function in the first place. But I agree, different thinking would be necessary.

 

[gemma-the-husky]

why as many as 30 arguments. when you get this many, it starts to sound like badly normalised data.

i may be wrong, but what ARE all the arguments?

 

[Pat Hartman]

Unless you are going to normalize the data, get used to writing lots of code.

Rather than passing in arguments from a query, turn the process inside out and have the sub loop through the recordset.