Multiple IIF statements in query


C

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!
 
Ad

Advertisements

S

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))
 
F

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.
 
G

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top