Part 2 of IIf function

  • Thread starter Thread starter Jack Houck
  • Start date Start date
J

Jack Houck

Here is my basic IIf function that I'm using.

q05z: IIf(Mid([q05],1,2)=1,"01"," ")

It's looking at the first 2 spaces and if it's equal to 1
then it puts "01", if not then it puts " ". Is it
possible to add to this string to have it compare those
first two spaces to see if it equals 2 (and then put "02"
or " ") or equals 3, etc...?
 
You can nest iif's here is a second level nesting

IIf(Mid([test],1,2)='01','1',IIf(Mid([test],1,2)='02','2','n1n2'))
basicly you are putting the new iif in the false section
I have writen one that was 5 levels deep. It worked, but it was very hard
to read. I think I resorted to calling a custom function and used vb code.
It was easier to read and debug
 
Jack,

You can nest IIf functions to 7 levels, for example...

q05z:
IIf(Mid([q05],1,2)=1,"01",IIf(Mid([q05],1,2)=2,"02",IIf(Mid([q05],1,2)=3,"03","
")

There is also the Switch() function, which is often simpler in therse
types of scenarios...

q05z:
Switch(Mid([q05],1,2)=1,"01",Mid([q05],1,2)=2,"02",Mid([q05],1,2)=3,"03")

Another approach that might work simpler is the Format() function, e.g. ...

q05z: Format(Mid([q05],1,2),"00")

However, it is not 100% clear what you are trying to do. I can't see
how Mid([q05],1,2) can ever evaluate to 1 or 2, so your expression will
never return anything. Is [q05] a number or text? Either way, how can
the first 2 digits be equal to 1?

Another side issue comment... it doesn't seem like a good idea to get
your expression to return a doublespace (or a single space, for that
matter) in the false-part argument of the function. What is your
purpose in doing this?
 
Back
Top