Parameter Arrays

[DJN]

I have several functions to perform statistical functions on fields in the same table. The number of fields is 100. The problem is that I get the error message “Expression you entered is too complex” if the number of fields exceeds 29. I can get functions to work in the debug window even when the number of arguments exceeds 29, up to 100. But as soon as I use the function, either as a calculated field in a query, e.g. Max([R1],[R2], etc.) or as the Control Source for a text box I run into the 29 number of fields barrier. I have tried the fields as text and number, but still the same result. Can anyone give me any idea as to how to overcome the 29 barrier?
David

 

[heckster]

You can avoid the 29 field barrier (which is actually a 256 character limit) by running your sql statement from code using the ...

DoCmd.RunSQL sqlstatement

You can create an SQL statement that is virtually unlimited (i.e.max length of the sql statement argument is 32,768 characters) in length. Have fun.

 

[Aivars]

Split your huge table and create relations between those ones. It will easier to work with data after them.

Aivars

 

[DJN]

Thanks Heckster, didn't realise the query had a 256 character limit, BUT do now!!

David