Expression too complex - More than 28 parameters

[InAFix]

Access 2k

I wrote a function to append data to a table based on a form where the
user could select from one of 24 payroll weeks or 3 special weeks
(total of 27 selections).
Old Function - Function sel401kWeek(WkNo, wk1, wk2, wk3, wk4, wk5,
wk6, wk7, wk8, wk9, wk10, _
wk11, wk12, wk13, wk14, wk15, wk16, Wk17, wk18, wk19, wk20, wk21, _
wk22, wk23, wk24, sp1, sp2, sp3)

The function has worked fine until I needed to expand to 8 special
weeks.

New Function - Function sel401kWeek(WkNo, wk1, wk2, wk3, wk4, wk5,
wk6, wk7, wk8, wk9, wk10, _
wk11, wk12, wk13, wk14, wk15, wk16, Wk17, wk18, wk19, wk20, wk21, _
wk22, wk23, wk24, sp1, sp2, sp3, sp4, sp5, sp6, sp7, sp8)

When I add the expression to the query I receive the "The expression
you entered is too complex" message.

I have no problem splitting out the additional special weeks to a sub
but I don't know how to pass the values for the sub to the query and
keep the results in one field. (The table is later used to create an
export file). Do I need a separate query with the special week
function?

tia for any ideas.

 

[david epsom dot com dot au]

That message normally means that you have an error, a bug, a
null, missing, or misspelled variable, a variable with the
wrong type or whatever. Are you sure it is because of the
number of function parameters???

(david)

 

[InAFix]

David - thank you for replying.

I did find that spliting out the function so that regular weeks ran
under one function and the special weeks under another, thereby
reducing the variables to less than 28 per function worked - and after
testing both ways, I couldn't find null, misspelled, wrong type as the
cause. I had found a kb article relating to access 97 that said 28
was the max, so I thoguht that still applied to Access 2k.

 

[david epsom dot com dot au]

Your right - I hadn't seen that limit before... And I get 29 :~)

(david)

 

[John Spencer (MVP)]

Could you use a parameter array to get around this limit. Since I have no idea
what your function does and if it is dependent on the position of the items you
pass in, I have no idea whether or not a parameter array would work for you.

Using a parameter array, I got no problems passing in 60 items

Fgetmaxnumber(1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0)

Where the fGetMaxNumber start as follows.

Public Function fGetMaxNumber(ParamArray Values()) As Variant
....