Use array in choose() function?

[tali]

I wonder if it's possible to use the array of some sort in the choose() function in MS Access.

For example instead of using the function like this

Choose(i, 1, 3, 5, 7, 9, 0)) Mod 10)

I would like to be able to keep in a table the array 1, 3, 5, 7, 9, 0 and the 10 value for Mod. So far I am not able to accomplish it with the array.

I can read the 1, 3, 5, 7, 9, 0 values from the table into a string, then can convert them via split into variant, but not able to add it to choose() function. I am not eager to do a lot of loops, case, etc, since I can have different lengths of arrays depending on which row from the table I am choosing.

Any suggestions?

[ADezii]

How about creating a New Choose Function?. Pass to it an Index and a pre-populated Array. The Function can now be used in an Expression.

Dim intCtr As Integer

'Create the Array and populate it
Dim aintTest(1 To 10) As Integer

For intCtr = 1 To 10
  aintTest(intCtr) = intCtr
Next

MsgBox (fChooseNew(5, aintTest()) ^ 2) Mod 10       'returns 5

Public Function fChooseNew(intIdx As Integer, aArgs() As Integer)
  fChooseNew = aArgs(intIdx)
End Function

[tali]

Sounds like a good option. Thanks!