IIf problems

[kpm_maloney]

I am working on a Microsoft Access database for a drug rehab clinic and this is what I need to do.

I need to beable to take DaysSpent and multiply by cost per day to get CostOfStay. What is throwing me off is there are two pricing catagories(Prov Service) and I cant figure out how to multiply by each pricing catagory.

Note:when ProvService=02, cost per day is $150, when ProvService=05, cost per day=200

This is my code so far...


CostOfStay: IIf([ProvService]=2,([DaysSpent]*150),IIf([ProvService]=5,([DaysSpent]*200)))

it is just showing #ERROR in the output.

Any ideas for a modifaction in my IIf statement, or am I going about the problem all wrong and using an IIf statement isnt something I should use in this situation.


Thanks in advance

[CaptainD]

IIf works like this. IIf(SomethingTo Evaluate, IfTrueValue, IfFalseValue)
You have two IIf's

Try:

CostOfStay = IIf([ProvService]=2,([DaysSpent]*150),([DaysSpent]*200)))

[wlc04]

I see you're nesting your IIF's, looks like code is okay except that you're missing the final statement in case neither of the iif's are true:


CostOfStay: IIf([ProvService]=2,([DaysSpent]*150),IIf([ProvService]=5,([DaysSpent]*200),SOMETHING GOES HERE))

[--Grrrrrr--]

Only other thing that might trip you up is the format of the ProvService field. If it is text you'll probably have to put it in double quotes. i.e. "02" or "05".