Multiple IIF statements in query

[Chris Fromm]

This is the query I'm trying to run:

1AA: IIf((Len([1stArrival]=6),
Val(Mid([1stArrival],3,2))),
IIf(Len([1stArrival]=7),
Val(Mid([1stArrival],4,2))))

The idea is to create a field called [1AA], which will
populate based on the field [1stArrival]. Since the
length of the strings in field [1stArrival] varies
between 6 and 7 characters or spaces, I'm using an IIF
statement cover both possibilities.

Each segment of the query works fine by itself, but when
I join them together (with a comma), I get an error. The
error says "Syntax error (comma) in query expression."
I've tried ways to get around it, but no go. Does anyone
know how to cover both conditions in the same query?
Thanks!

 

[Steve Schapel]

Chris,

You have the ) in the wrong position on the Len() function, and you have
a rogue ( in the beginning. This should work...
1AA:
IIf(Len([1stArrival])=6,Val(Mid([1stArrival],3,2)),IIf(Len([1stArrival])=7,Val(Mid([1stArrival],4,2))))
Using the Switch function would possibly be neater...
1AA:
Switch(Len([1stArrival])=6,Val(Mid([1stArrival],3,2)),Len([1stArrival])=7,Val(Mid([1stArrival],4,2)))
But, if the length of 1stArrival is always either 6 or 7, this may
suffice...
1AA:
IIf(Len([1stArrival])=6,Val(Mid([1stArrival],3,2)),Val(Mid([1stArrival],4,2)))
In the end, though, this is how I would do it...
1AA: Val(Mid([1stArrival],Len([1stArrival])-3,2))

 

[fredg]

This is the query I'm trying to run:

1AA: IIf((Len([1stArrival]=6),
Val(Mid([1stArrival],3,2))),
IIf(Len([1stArrival]=7),
Val(Mid([1stArrival],4,2))))

The idea is to create a field called [1AA], which will
populate based on the field [1stArrival]. Since the
length of the strings in field [1stArrival] varies
between 6 and 7 characters or spaces, I'm using an IIF
statement cover both possibilities.

Each segment of the query works fine by itself, but when
I join them together (with a comma), I get an error. The
error says "Syntax error (comma) in query expression."
I've tried ways to get around it, but no go. Does anyone
know how to cover both conditions in the same query?
Thanks!

If the [1stArrival] is always either 6 or 7 characters you don't need
the second IIF.
Anyway, you have your parenthesis in the wrong places.

An IIf() function works like this:
IIf([SomeField] = SomeCriteria,True portion, False portion)

Try it like this:

1AA:IIf(Len([1stArrival])=6,Val(Mid([1stArrival],3,2)),
Val(Mid([1stArrival],4,2)))


If the length is 6 you will get the 3rd and 4th character value,
otherwise the 4th and 5th values.

 

[Guest]

Hi Chris,

To combine iif() functions, you nest them within the true or false part of
previous ones. In this case, it would be something like:

1AA:
IIf(Len([1stArrival]=6),Val(Mid([1stArrival],3,2)),IIf(Len([1stArrival]=7),Val(Mid([1stArrival],4,2)),0))

I took out some extra ()'s, and also added the false condition for the
second iif(). Note that the second iif() function is in the false argument
section of the first.

But, I think you can also accomplish what you are trying to do more simply
by using the len() funtion inside of your Mid function as follows:

1AA: Mid([1stArrival],len([1stArrival])-3,2)

HTH, Ted Allen

 

[Chris Fromm]

You all were very helpful!