Nested Iif

[mbr96 via AccessMonster.com]

I'm trying to build an expression in a query that will test two fields and
reutrn a value based on the following logic:
*******
If the 1st digit of the LeaseNum field is "S", and

If the CompanyCode is 78, return "SOCA-08", if not, return "SOCA-01"

If the 1st digit of the LeaseNum field is "N", and

If the company code is 78, return "NOCA-08", if not return "NOCA-01"
*******

So far, I have

CustNum: IIf(Left([LeaseNum],1)="S",IIf([CompanyCode]="78","SOCA-08","SOCA-
01"))

but that just works for the "S" LeaseNums. Not sure how to nest another Iif,
or use a Case statement somehow??

Help please?
MBR

 

[Jeff Boyce]

After the IIF tests for "S", you have an expression of what to do if =S ...
but no expression of what to do if it doesn't =S, (or if it does =N). Add a
comma and the "what to do if false" expression.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

One method

IIF(LeaseNum Like "S* And CompanyCode ="78","SOCA-08",
IIF(LeaseNum Like "N*" and CompanyCode="78", "NOCA-08",
IIF(LeaseNum Like "S*" and CompanyCode <>"78",SOCA-01"
IIF(LeaseNum Like "N*" and CompanyCode<>"78", "NOCA-01",Null))))

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[mbr96 via AccessMonster.com]

Thanks. I tried

CustNum: IIf(Left([LeaseNum],1)="S",IIf([CompanyCode]=78,"SOCA-08","SOCA-01",
IIf([CompanyCode]=78,"NOCA-08","NOCA-01"))

but it's saying it's the wrong number of arguments. If the test for S fails,
it still needs to test for company 78 to return NOCA-08 or NOCA-01 if not.
Don't quite have my brain around this yet. What am I doing wrong?

MBR

After the IIF tests for "S", you have an expression of what to do if =S ...
but no expression of what to do if it doesn't =S, (or if it does =N). Add a
comma and the "what to do if false" expression.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm trying to build an expression in a query that will test two fields and
reutrn a value based on the following logic:

[quoted text clipped - 20 lines]

Help please?
MBR

 

[mbr96 via AccessMonster.com]

Thanks too - giving me "invalid syntax" on that method, selecting the first
"78". Something about the "Like" and "And" parts seem suspect too. The
logic looks like what I want, though, I'll keep playing with it.

One method

IIF(LeaseNum Like "S* And CompanyCode ="78","SOCA-08",
IIF(LeaseNum Like "N*" and CompanyCode="78", "NOCA-08",
IIF(LeaseNum Like "S*" and CompanyCode <>"78",SOCA-01"

I'm trying to build an expression in a query that will test two fields and
reutrn a value based on the following logic:

[quoted text clipped - 20 lines]

Help please?
MBR

 

[mbr96 via AccessMonster.com]

That did it - I had picked up the carriage returns (remember that term???)
and played with the syntax a little, but I got it.

Thanks!!
MBR

One method

IIF(LeaseNum Like "S* And CompanyCode ="78","SOCA-08",
IIF(LeaseNum Like "N*" and CompanyCode="78", "NOCA-08",
IIF(LeaseNum Like "S*" and CompanyCode <>"78",SOCA-01"

I'm trying to build an expression in a query that will test two fields and
reutrn a value based on the following logic:

[quoted text clipped - 20 lines]

Help please?
MBR