#Func! error in a query

Question

Here is the expression that produces the error at index 1:  

FName: Choose([SpaceCount],Mid([EmpName_Parsed],[1stSpacePosition]+1,[StringLength]-[1stSpacePosition]),Mid([EmpName_Parsed],[1stSpacePosition]+1,[2ndSpacePosition]-([1stSpacePosition]+1)),"")

The [SpaceCount] result in this data set produces a 1, 2 or 3.  It is the result of this expression:   Len([EmpName_Parsed])-Len(Replace([EmpName_Parsed]," ",""))

The puzzling thing is that index 1 of the choose function works perfectly is you replace the second Mid Function with "".  Put the second mid function in the choose function at index 2 and the second index works but the index 1 Mid function returns "#Func!

I replaced the choose function with:  IIf([SpaceCount]=1,Mid([EmpName_Parsed],[1stSpacePosition]+1,[StringLength]-[1stSpacePosition]),IIf([SpaceCount]=2,Mid([EmpName_Parsed],[1stSpacePosition]+1,[2ndSpacePosition]-([1stSpacePosition]+1)),""))

This works fine.  But, why does the Choose function produce the error?

Answer 1

I would need to have some actual values to test.

Answer 2

Thanks for the reply.  I had the same idea when trouble shooting this.  The Mid functions operate perfectly outside the choose function.  They operate and produce the desired result as its own column and they operate in the IIF function just fine.  When they are both in the Choose function it fails at index position 1 only.  But, if the one that fails with the function at index 1 when both are in the function, is by itself with not other functions at the other index locations it works fine.  That is the part that throws me and makes me believe it is a program bug.

Answer 3

I would split out some of the expressions from the Choose() function to create their own columns so you can see what's going on inside the expression.