Using IIF in a report

[angie]

=IIf([Rate]>65,([Rate]-65)*0.15,0)
I have a text box that I use this in the controlsource. This is used on a report that isused to calculate a total to pay. I needto add to the above code the following.
I have a field called “hours” that hold a number so I needto add to the above something like this
=IIf([Rate]>73,([Rate]-65)*0.15,0),IIF([hours]is notnull,([hours]*0.20,0))

So what I am trying to say is that if the hours field is notnull and there is a number in the field than I want it to multiply the hoursfield by 20.00

[CJ_London]

'[Hours] is not null' is used in sql, for vba you use isnull([Hours])

I don't understand the description of what you are trying to do -

if the hours field is notnull and there is a number in the field

which field? if it is not null then there will be a value in the field.

Perhaps provide an example with some data

[angie]

Sorry I gave you the wrong thing... I use this and it works great
=IIf([type of call]="warranty",14,IIf([type of call]="inspection",14,20))
But I would like to add the following so that the field will multiply the field hours by 20.00
IIF([type of call]=”install part”,([hours])*0.20,0)))
I just cant get it to work
thanks sorry again for posting the wrong thing

[CJ_London]

just as confusing - provide some example data and the required outcome

[angie]

I tried to upload a picture of the report but for some reason it will not let me. so I am trying to explain better.

I have a report and on the report there is a text box that has the following in the control source
=IIf([type of call]="warranty",14,IIf([type of call]="inspection",14,20))
this code works find but I would like to add the following to the above control source. ( so the type of call " warranty or "inspection" would get a default of 14.00 and the " Install Part" would get the "HOURS" field number multiply by 20.00

IIF([type of call]=”install part”,([hours])*0.20,0)))
I have fields that include the following : "Type of Call" "Hours" and some others.
This changes the information in the text box depending on what is entered in the "type of call" text box.
So If the type of call is "warranty or inspection is will have 14.00 value. I want to to also include the type of call Install Part and calculate the Hours text box by 20.00.
So if the type of call is "install part" and there is 2 hours in the "hours text box" than it would multiply 2 hours by 20.00 and the value would be 40.00

[CJ_London]

so my understanding is

typeofcall...hours...value required
warranty....n/a......14
inspection..n/a......14
installpart...null.....0
installpart...2........hours*20=40

if so then suggest

=iif([typeofcall] = "warranty" OR [typeofcall] = "inspection",14,nz(hours,0)*20)

[angie]

so my understanding is

typeofcall...hours...value required
warranty....n/a......14
inspection..n/a......14
installpart...null.....0
installpart...2........hours*20=40

if so then suggest

=iif([typeofcall] = "warranty" OR [typeofcall] = "inspection",14,nz(hours,0)*20)

Thank you, this worked but where it would default to 20.00 it is no longer doing this. could you please help me with this also I tried to add ,20 after the 14 but it didn't work
thanks Angie

[Pommetje77]

Hello Angle,
It looks like we don't really understand you.
Wat is the error code? is there an error code at all?
Show us a screen shot or a small example!
Try using ; instead of ,
Be as clear as possible please!

[davegri]

I stay away from iif unless I can't, such as required by a query. In VBA I like select case. Much easier to visualize and debug.
Below may be based on incorrect assumptions, but will be easy to fix.

dim rslt as long
select case [typeofcall]
    case "warranty", "inspection"
        rslt = 14
    case else
        select case nz(installpart,0)
            case 0
                rslt = hours
            case else 
                rslt = hours * 2
         end select
end select

[angie]

Yes this is correct but I am missing one thing

If typeofcall is anything else I want it to default to 20.
The above code works on all the cases you have but I just need to add a default of 20 for any other typeofcall.

typeofcall...hours...value required
warranty....n/a......14
inspection..n/a......14
installpart...null.....0
installpart...2........hours*20=40

any other type....n/a....20 (this would be the default if any other type of call)

Thank you

[ssanfu]

PMFJI,
here are some modifications to the code/formula .......

dim rslt as long
select case [typeofcall]
    case "warranty", "inspection"
        rslt = 14
    case "installpart"
        rslt = nz(hours,0) * 20
    case else
        rslt = 20  'default to 20
end select

and fir IIF():

=IIF([typeofcall] = "warranty" OR [typeofcall] = "inspection",14,IIF([typeofcall] = "installpart",nz(hours,0)*20, 20))

[angie]

Thank you this worked perfect.....

[CJ_London]

the access sql equivalent to the case statement is the switch function

so the equivalent of this
=IIF([typeofcall] = "warranty" OR [typeofcall] = "inspection",14,IIF([typeofcall] = "installpart",nz(hours,0)*20, 20))

would be
=switch([typeofcall] = "warranty" OR [typeofcall] = "inspection",14,[typeofcall] = "installpart",nz(hours,0)*20, true, 20)