error when trying to use IIF and LEN

[baseball]

[runner_1] either contains an alphanumeric id of the form xxxxxnnn or Is Null. There are also similar fields, [runner_2] and [runner_3]. There are many id's but they are not important to my query. I want to look at the eight variations from no runners on base to runners on each base, so I thought I could simply add fields that replace the id's with 0/1, 0/2, 0/3 and then concatenate those three fields (Expr1&Expr2&Expr3).

To that end I built the following expression(s):
Expr1: IIf(Len([runner_1b]>1),1,0) ...etc.

However, when I run the query I get #Error in all fields when Len([runner1]) is not null. When it is null, I get a 0. I don't understand what the error represents and why I am not getting a 1.

I could simply write a query that provides the basic data I need, then export it to Excel and solve the problem but I am curious as to why I cannot make it work in Access.

I'm using Access 2007 on a Win 8.1 machine and I use the Query Design tool and Builder in Access, not SQL.

 

[Micron]

I think it's because you're trying to evaluate the Len function against [runner_1b]>1 and NOT [runner_1b] as you are no doubt trying to do. Try
Expr1: IIf(Len([runner_1b])>1,1,0). You get the zero because your expression evaluates to false if the field is NULL. If it's not NULL, it throws an error because the evaluation has no meaning, and it can never be true.

 

[baseball]

I see. I had the parenthesis in the wrong place. It works if I write it as Expr1: IIf(Len([runner_1b])>1,1,0). I probably should have figured that out myself but sometimes I don't see the forest for the trees. Thank you for helping me get on track.

Cliff

 

[Micron]

You're welcome. I have been in that forest before.