IIF issue

[mohiahmed]

Hi everyone,



I am trying to add a calculated field by using the below code:

IIf([Post Code]=E11,5,IIf([Post Code]=E10,15))

I keep getting a syntax error - can anyone help?

Thank you

[alansidman]

here is the proper syntax.

http://www.techonthenet.com/access/f...vanced/iif.php

[mohiahmed]

Thank you,

What about if there were more than one condition? I.e. More than one post code?

[alansidman]

If there is more than one condition, then you need to nest your iif statment.

Here is the syntax:

=IIf(firstCriteria, TrueValue for first, IIf(SecondCriteria, TrueValue for Second, False Value for both))

You can add additional IIFs in the False Value, but the last value must be the false for all. If you have more than seven nests (I believe) you should then switch to VBA and use IF-THEN or Case Select.

You can google these items for further explanation

[mohiahmed]

I will try it out but I thought I was using nested iif statement

[mohiahmed]

Thank you...I tried this:

=IIf([Post Code], E18, 10, IIf([Post Code], E17, 5, IIf([Post Code], E16, 5, 0))

What I wanted is if the value for postcode is E18 then 10 should appear, if it is E17 then 5 should appear, if it is E16 then 5 should also appear and if none of the three postcodes then 0 should appear.

I get a "Wrong number of arguments" error? What am I doing wrong?

[mohiahmed]

...I finally got it to work!

Thank you very much Alan!

Now my biggest problem....new thread time!

[mohiahmed]

...Ahh!! It was working but now all that appears in the field is "#error"?

This is the code I was using and it worked but now it doesn't?

Sib P: IIf([Number of Sibling(s)]="1","15",IIf([Number of Sibling(s)]="2","25",IIf([Number of Sibling(s)]>"2","30","0")))

[ssanfu]

...Ahh!! It was working but now all that appears in the field is "#error"?

This is the code I was using and it worked but now it doesn't?

Sib P: IIf([Number of Sibling(s)]="1","15",IIf([Number of Sibling(s)]="2","25",IIf([Number of Sibling(s)]>"2","30","0")))

Why are ther quotes around the numbers? Is [Number of Sibling(s)] a text field?
If it is not a string (text), you should use:

Sib P: IIf([Number of Sibling(s)]= 1, 15, IIf([Number of Sibling(s)]= 2, 25, IIf([Number of Sibling(s)]> 2, 30, 0)))

2 is numeric
"2" is a string
therefore
"2" <> 2

BTW, you shouldn't use spaces or special characters in object names....

[mohiahmed]

Thank you, it works fine now!

SSAnFu do you have any idea how to solve the issue I am having in my other thread?