IIf, IIf, IIf and another IIf

[fibayne]

This was working really (below..) well until I added a fourth IIf, havin searched the forum the general concensus would be to avoid IIf,s like the plague I have seen posts on Switch as an alternative could anyone point me in the right direction / help me out ..please

Expr2: IIf(([CPM])='CPA',
IIf(([CPM])='CPC',
IIf(([CPM])='CPM',
[Quantity]*[CostPrice]),
[Quantity]*[CostPrice]),
[Quantity]/1000*[CostPrice])

Expr2: IIf(([CPM])='CPA',
IIf(([CPM])='CPC',
IIf(([CPM])='CPM',
IIf(([CPM])='Fixed',
[Quantity]*[CostPrice]),
[Quantity]*[CostPrice]),
[Quantity]/1000*[CostPrice]),
[Quantity]/100*[CostPrice])

thanks again

 

[Banana]

Investigate "Switch" Function or "Choose" Function in Access Help or Microsoft Knowledge Base.

 

[fibayne]

IIF or Switch

Thanks for the advice Banana, I will look it up, I just tried something Brian Warnock had suggested earlier

Expr1: IIf([Deal]='CPA',[Quantity]*10,IIf([Deal]='CPM',[Quantity]*100,IIf([Deal]='CPA',[Quantity]*1000)))

ie 3 brackets at the end, I have put 4 at the end of my IIf and ...its working ...for time being I suspect

thanks for your help
Fi

 

[Moniker]

The SWITCH is a lot easier to read and maintain:

Expr1: Switch([Deal]='CPA',[Quantity]*10,[Deal]='CPM',[Quantity]*100,[Deal]='CPA',[Quantity]*1000,True,0)

However, in your sample nest IIF statement, you have Deal='CPA' twice. The second one will never be reached as the first one will always return true.

 

[Brianwarnock]

However, in your sample nest IIF statement, you have Deal='CPA' twice. The second one will never be reached as the first one will always return true.

I pointed this out to fibayne on the other thread, I assume its a typo in the post, what worries me is the way he constructs his IIf in post 1, I told him before that it would cause him problems, I wonder if he really understands IIf and the nesting of them, perhaps hw will do better with the Switch, I must look at that.

Brian

 

[Moniker]

SWITCH is easy to read and maintain once you understand what's going on. It works like this:

SWITCH(logical_test1, test1_is_true, logical_test2, test2_is_true, ... logical_testX, testX_is_true)

It looks at the first logical test. If it's true, it returns the value in "test1_is_true". If it's not true, it keeps on going until it finds a logical test that results in true. That explains why at the end of my SWITCH, I put "True, 0". If the switch gets all the way to that point, then it will evaluate True=True, and return a 0.

Just like with Iif, your tests and values do not have to be numeric. They can be strings, other functions, variables, etc.

For example, if you wrote a boolean function to test to see if a file exists in a certain location that returns TRUE if the file exists, you could write something like this:

FileStatus = Switch(FindFileFunction, "File exists", True, "File doesn't exist")

(The "If..Then" way to read that is, "If FindFileFunction=True Then 'File exists' Else 'File doesn't exist'".)

That in turn can be used in queries, macros, etc.

 

[Brianwarnock]

Thanks moniker, you explanation makes it very simple, thanks for your trouble

Brian

 

[theprez]

Just a thought, but would a Case Select statement work?

 

[Moniker]

Glad it helped Brian.

Prez, Select Case would work, but since you're only looking for two values in my example, it's overkill.

    Select Case FindFileFunction
        Case True
            <File Exists>
        Case False
            <File Doesn't Exist>
        Case Else
    End Select

Select Case is better used when you're expecting certain results, but you're not exactly which one might come, or if any of your expected results will come at all. A clear example of this would be an error handling routine, something like this:

Error_Handling_Routine_Name:

    Select Case Err.Number
        Case 3011 [COLOR="Green"]'The Microsoft Jet database engine could not find the object[/COLOR] 
            MsgBox "Couldn't find the object."
        Case 3024 [COLOR="Green"]'Could not find file[/COLOR] 
            MsgBox "Couldn't find the file."
        Case Else [COLOR="Green"]'Some other error happened[/COLOR]
            MsgBox "Something else is broken - " & Err.Number & " " & Err.Description
    End Select

    Resume Next

The above example shows where you might be expecting to not find an object or a file, but bugs being what they are, you may get an error that is neither of the expected errors. In this case, the Select Case statement handles both expected and unexpected outcomes.

Select Case is also good if you want to perform a lot of actions based on a certain value. For example, you may have a set of option buttons. If option A is selected, then you want to provide the end user with a way to load a text document, providing them with an interface to do so. If, however, option 2 is selected, you may want to instead provide them with a way to write a custom text document.

Select Case Option_Button_Value
    Case 1
        <the load routine and all its code and functions here>
    Case 2
        <the edit routine and all its code and functions here>
    Case Else
End Select

Unlike Switch, Select Case can make function/subroutine calls based on the values of certain other variables. Switch sets a value based on logical conditions. Select Case can do this, but it can also be used a branching device to move code execution in a different direction.

 

[Banana]

Wait a minute-

I thought Select Case isn't supported in Access queries? I thought it was strictly a VBA method?

 

[Moniker]

That is true. Select Case is not available in a query. A function that uses a Select Case is allowed in a query because a function will return a value of some sort. However, that's an awfully awkward way to do things, and should be avoided.

 

[KeithG]

Yes that is true Select Case is just supported in VBA. But you could create a VBA function to use a SElect case statement in a query