Changing output of IIf statement in SQL from -1,0 to a text value

Posted on 2011-04-28
Last Modified: 2012-05-11
Hello, I have the following nested IIf statement:
New Reviewed Item: IIf(([PPopulation] And [DDesign1] And [O1Outcome1]),True,IIf(([PPopulation] And [DDesign1] And [D2Design2] And [O2Outcome2] And [SP_YN]),True,IIf(([PPopulation] And [DDesign1] And [D2Design2] And [O2Outcome2] And Not [SP_YN] And ([NTotal]>=100)),True,False)))

Open in new window

which works very well and gives me the value -1 for true and 0 for false.  Instead of these values I would like to get the code INC for -1 and EXC for 0.  Can anyone show me how to modify this statement to get this effect?

Thanks so much,
Question by:Bevos
    LVL 119

    Expert Comment

    by:Rey Obrero
    try this

    New Reviewed Item: IIf(([PPopulation] And [DDesign1] And [O1Outcome1]),True,IIf(([PPopulation] And [DDesign1] And [D2Design2] And [O2Outcome2] And [SP_YN]),True,IIf(([PPopulation] And [DDesign1] And [D2Design2] And [O2Outcome2] And Not [SP_YN] And ([NTotal]>=100)),"INC","EXC")))
    LVL 9

    Accepted Solution

    Just put the whole IIf in another IIF statement ...

    IIf(oldstatement = true, "INC", "EXC")


    Author Comment

    Hi Capricorn, when I try that statement I get -1 still for true values and #Error when the value is 0.  The SQL code is accepted by the program however without error.

    Author Comment

    That worked sshah :) thanks so much both of you for your comments.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now