nesting IIF Statement


G

Guest

I have a query that I created a new filed
Codes2:
I get confused on how to do 2 iff statements in one.
IIF([I/O_Travel]="O",[Out of State Codes.Code])
IIF([I/O_Travel]="I",[InState Codes.Codes])
I am not sure how to combine these.

Thanks
Chey
 
Ad

Advertisements

G

Guest

Hi Chey

If the answer can be either (but only) "O" or"I" you don't need to "nest"
Use this

IIF([I/O_Travel]="O",[Out of State Codes.Code],[InState Codes.Codes])

This means that if [I/O_Travel] is anything other than "O" the result will
be the contents of field [InState Codes.Codes]


If the answer was for example [OtherState Codes.Codes]
Then could used a nested if like this

IIf ( ([I/O_Travel]="O", [Out of State Codes.Code], IIf ( ([I/O_Travel]="I",
[InState Codes.Codes], [OtherState Codes.Codes]) )

I think you can have up to 7 nested IIF's - although in that case there
would be better ways to get the results you need.

Hope this helps

--
Wayne
Manchester, England.
Enjoy whatever it is you do
Scusate,ma il mio Inglese fa schiffo :)
Percio se non ci siamo capiti, mi mandate un
messagio e provero di spiegarmi meglio.
 
G

Guest

So after I posted this I realized exactly what I needed. It will end of
being nested
I/O_Travel=I AND Employee=Yes then InState Code
I/O_Travel-O AND Employee=Yes then Out of State Code
I/O_Travel=I AND Employee=No then Non Employee In State Code
I/O_Travel=O AND Employee=No then non employee Out of State Code.
Thanks for all your help
Chey

Wayne-I-M said:
Hi Chey

If the answer can be either (but only) "O" or"I" you don't need to "nest"
Use this

IIF([I/O_Travel]="O",[Out of State Codes.Code],[InState Codes.Codes])

This means that if [I/O_Travel] is anything other than "O" the result will
be the contents of field [InState Codes.Codes]


If the answer was for example [OtherState Codes.Codes]
Then could used a nested if like this

IIf ( ([I/O_Travel]="O", [Out of State Codes.Code], IIf ( ([I/O_Travel]="I",
[InState Codes.Codes], [OtherState Codes.Codes]) )

I think you can have up to 7 nested IIF's - although in that case there
would be better ways to get the results you need.

Hope this helps

--
Wayne
Manchester, England.
Enjoy whatever it is you do
Scusate,ma il mio Inglese fa schiffo :)
Percio se non ci siamo capiti, mi mandate un
messagio e provero di spiegarmi meglio.


Chey said:
I have a query that I created a new filed
Codes2:
I get confused on how to do 2 iff statements in one.
IIF([I/O_Travel]="O",[Out of State Codes.Code])
IIF([I/O_Travel]="I",[InState Codes.Codes])
I am not sure how to combine these.

Thanks
Chey
 
G

Guest

IIf ( [I/O_Travel]="I" AND [Employee]= "Yes", [InState Code], IIf (
[I/O_Travel]="O" AND [Employee]="Yes", [Out of state code], IIf (
[I/O_Travel]="I" AND [Employee]="No", [Non Employee In State Code],IIf (
[I/O_Travel]="O" AND [Employee]="No", [Non Employee Out of state Code]))))

This assumes that the YES and NO field is a text field. If it's a tick
(check) box you may want to use this

IIf ( [I/O_Travel]="I" AND [Employee]= -1, [InState Code], IIf (
[I/O_Travel]="O" AND [Employee]=-1, [Out of state code], IIf (
[I/O_Travel]="I" AND [Employee]=0, [Non Employee In State Code],IIf (
[I/O_Travel]="O" AND [Employee]=0, [Non Employee Out of state Code]))))

I think that these are not the correct field names but I have used them as
you gave them in your example
[InState Code]
[Out of state code]
[Non Employee In State Code]
[Non Employee Out of state Code]






--
Wayne
Manchester, England.
Enjoy whatever it is you do
Scusate,ma il mio Inglese fa schiffo :)
Percio se non ci siamo capiti, mi mandate un
messagio e provero di spiegarmi meglio.


Chey said:
So after I posted this I realized exactly what I needed. It will end of
being nested
I/O_Travel=I AND Employee=Yes then InState Code
I/O_Travel-O AND Employee=Yes then Out of State Code
I/O_Travel=I AND Employee=No then Non Employee In State Code
I/O_Travel=O AND Employee=No then non employee Out of State Code.
Thanks for all your help
Chey

Wayne-I-M said:
Hi Chey

If the answer can be either (but only) "O" or"I" you don't need to "nest"
Use this

IIF([I/O_Travel]="O",[Out of State Codes.Code],[InState Codes.Codes])

This means that if [I/O_Travel] is anything other than "O" the result will
be the contents of field [InState Codes.Codes]


If the answer was for example [OtherState Codes.Codes]
Then could used a nested if like this

IIf ( ([I/O_Travel]="O", [Out of State Codes.Code], IIf ( ([I/O_Travel]="I",
[InState Codes.Codes], [OtherState Codes.Codes]) )

I think you can have up to 7 nested IIF's - although in that case there
would be better ways to get the results you need.

Hope this helps

--
Wayne
Manchester, England.
Enjoy whatever it is you do
Scusate,ma il mio Inglese fa schiffo :)
Percio se non ci siamo capiti, mi mandate un
messagio e provero di spiegarmi meglio.


Chey said:
I have a query that I created a new filed
Codes2:
I get confused on how to do 2 iff statements in one.
IIF([I/O_Travel]="O",[Out of State Codes.Code])
IIF([I/O_Travel]="I",[InState Codes.Codes])
I am not sure how to combine these.

Thanks
Chey
 
G

Guest

Thanks works great!!!
Chey

Wayne-I-M said:
IIf ( [I/O_Travel]="I" AND [Employee]= "Yes", [InState Code], IIf (
[I/O_Travel]="O" AND [Employee]="Yes", [Out of state code], IIf (
[I/O_Travel]="I" AND [Employee]="No", [Non Employee In State Code],IIf (
[I/O_Travel]="O" AND [Employee]="No", [Non Employee Out of state Code]))))

This assumes that the YES and NO field is a text field. If it's a tick
(check) box you may want to use this

IIf ( [I/O_Travel]="I" AND [Employee]= -1, [InState Code], IIf (
[I/O_Travel]="O" AND [Employee]=-1, [Out of state code], IIf (
[I/O_Travel]="I" AND [Employee]=0, [Non Employee In State Code],IIf (
[I/O_Travel]="O" AND [Employee]=0, [Non Employee Out of state Code]))))

I think that these are not the correct field names but I have used them as
you gave them in your example
[InState Code]
[Out of state code]
[Non Employee In State Code]
[Non Employee Out of state Code]






--
Wayne
Manchester, England.
Enjoy whatever it is you do
Scusate,ma il mio Inglese fa schiffo :)
Percio se non ci siamo capiti, mi mandate un
messagio e provero di spiegarmi meglio.


Chey said:
So after I posted this I realized exactly what I needed. It will end of
being nested
I/O_Travel=I AND Employee=Yes then InState Code
I/O_Travel-O AND Employee=Yes then Out of State Code
I/O_Travel=I AND Employee=No then Non Employee In State Code
I/O_Travel=O AND Employee=No then non employee Out of State Code.
Thanks for all your help
Chey

Wayne-I-M said:
Hi Chey

If the answer can be either (but only) "O" or"I" you don't need to "nest"
Use this

IIF([I/O_Travel]="O",[Out of State Codes.Code],[InState Codes.Codes])

This means that if [I/O_Travel] is anything other than "O" the result will
be the contents of field [InState Codes.Codes]


If the answer was for example [OtherState Codes.Codes]
Then could used a nested if like this

IIf ( ([I/O_Travel]="O", [Out of State Codes.Code], IIf ( ([I/O_Travel]="I",
[InState Codes.Codes], [OtherState Codes.Codes]) )

I think you can have up to 7 nested IIF's - although in that case there
would be better ways to get the results you need.

Hope this helps

--
Wayne
Manchester, England.
Enjoy whatever it is you do
Scusate,ma il mio Inglese fa schiffo :)
Percio se non ci siamo capiti, mi mandate un
messagio e provero di spiegarmi meglio.


:

I have a query that I created a new filed
Codes2:
I get confused on how to do 2 iff statements in one.
IIF([I/O_Travel]="O",[Out of State Codes.Code])
IIF([I/O_Travel]="I",[InState Codes.Codes])
I am not sure how to combine these.

Thanks
Chey
 
Ad

Advertisements

S

Steve Schapel

This is fine, but I would tend to prefer the Switch function in such a
case...

Switch([I/O_Travel]="I" And [Employee],[InState Code],[I/O_Travel]="O"
And [Employee],[Out of state code],[I/O_Travel]="I" And Not
[Employee],[Non Employee In State Code],[I/O_Travel]="O" And Not
[Employee], [Non Employee Out of state Code])

--
Steve Schapel, Microsoft Access MVP

Wayne-I-M said:
IIf ( [I/O_Travel]="I" AND [Employee]= "Yes", [InState Code], IIf (
[I/O_Travel]="O" AND [Employee]="Yes", [Out of state code], IIf (
[I/O_Travel]="I" AND [Employee]="No", [Non Employee In State Code],IIf (
[I/O_Travel]="O" AND [Employee]="No", [Non Employee Out of state Code]))))

This assumes that the YES and NO field is a text field. If it's a tick
(check) box you may want to use this

IIf ( [I/O_Travel]="I" AND [Employee]= -1, [InState Code], IIf (
[I/O_Travel]="O" AND [Employee]=-1, [Out of state code], IIf (
[I/O_Travel]="I" AND [Employee]=0, [Non Employee In State Code],IIf (
[I/O_Travel]="O" AND [Employee]=0, [Non Employee Out of state Code]))))

I think that these are not the correct field names but I have used them as
you gave them in your example
[InState Code]
[Out of state code]
[Non Employee In State Code]
[Non Employee Out of state Code]
 
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

Similar Threads

nesting Iff statement 2
Visable 12
Iff statement to show pictures 4
Query Criteria IIf statement 4
Group totals 6
Multiple iif challenge 2
IIf statements 3
iif statement 5

Top