Choose Function Limitations

[Josh330]

Hi there. Are there limitations to the Choose function? Such as maximum number of values? For the values, I am using fields. I am asking because I am using the Choose function in many fields in a Query table. It works perfectly, but then for one field it continues to show an #Error message. This happens when I reach 10 values in the list. I have spent hours examining the issue and I cannot figure it out. I think it has something to do with the Choose function. Thanks.

[June7]

I just tested Choose with 26 elements in VBA and it works.

There is a limit of 1024 characters in cell of query design grid.

Post your code.

[Josh330]

Hi June.

The below formula works in the Query. The output is a date field. Please excuse the unsophisticated field names.

Task 010 Start: [Task 010 Gap]+Choose([Task 010 Predecessor],[Task 001 End],[Task 002 End],[Task 003 End],[Task 004 End],[Task 005 End],[Task 006 End],[Task 007 End],[Task 008 End],[Task 009 End])

The one below results in an #Error. This is pretty much identical to the one above. The added fields I have checked a thousand times.

Task 011 Start: [Task 011 Gap]+Choose([Task 011 Predecessor],[Task 001 End],[Task 002 End],[Task 003 End],[Task 004 End],[Task 005 End],[Task 006 End],[Task 007 End],[Task 008 End],[Task 009 End],[Task 010 End])

Thanks.

[June7]

That looks like a non-normalized data structure.

See nothing wrong with expression. Now I would have to examine data. If you want to provide db for analysis, follow instructions at bottom of my post.

[moke123]

What's the value of Task 011 Predecessor? If it's null or greater than the number of choices you'll get an error, I believe.

[June7]

moke, I tested both. If index is Null, does error. If the index is greater than the number of elements, that just returns Null.

If Null were the issue, wouldn't it error without the 10th element?

[moke123]

@ June7 - This is probably the first time I've looked at the Choose() in 10 or so years so I'm not sure what it would do.
If it's a date datatype I don't think it can be null. Also not sure what [Task 011 Gap] is as it is being Added (+) to a date. [Task 011 Gap]+Choose([ . . .
Anything plus null = null.

This errors

Dim x As Date
x = Choose(4, #1/1/2021#, #2/2/2022#, #3/3/2023#)
Debug.Print x

this does not error. x as variant datatype which can be null

Dim x 
x = Choose(4, #1/1/2021#, #2/2/2022#, #3/3/2023#)
Debug.Print x

[June7]

Well, the calc seems to be in query so a variable type is not a concern.