IIf with multiple conditions on a calculated text box?

[SirStevie3]

On a subform I have a Rate box and a Rate_Type box. both are already populated.

Rate_type can be "day","session", or "hour"

I have a Total Charges text box where i would like the... uh... total charges to be displayed.

so i know if the type were "day" or "session" i would just mirror the value of Rate in Total Charges. but if it were by hour i would have to do some calculations.

i've never done an IIf statement before, and its proving to be a little difficult to grasp. can someone point me in the right direction?

ps. if the type is hour, there are also time_start and time_end controls that would be used to calculate how many hours to multiply the rate by. it would be helpfull to round to the next 15 minutes also but i'll cross that bridge when i get there... for now im just concerned with the if then thing

 

[Roku]

On a subform I have a Rate box and a Rate_Type box

What type of boxes are these (Combo, List, Text)? The answer will shape the way to tackle the solution.

I shall assume Text boxes for now, so try this:

Dim strResult As String
strResult = IIf(Nz(Rate_Type, "") = "", "Empty result", IIf(Rate_Type = "hour", "'Hour' result", "'Day or Session' result"))
MsgBox strResult

I have used nested IIf statements here. The outer one checks that the TextBox has a value and returns "Empty result" if it doesn't.
If the TextBox has content, the second IIf tests for "hour" (as it's the odd one out, which needs the calculation). Here, the result is one of two string values.

You can write the above more simply as:

MsgBox IIf(Nz(Rate_Type, "") = "", "Empty result", IIf(Rate_Type = "hour", "'Hour' result", "'Day or Session' result"))

This is not a practical use of IIf for normal purposes. Where the string "Hour result" appears, substitute the calculation you need; in the place where "Day or Session result" appears, use Rate (i.e. your source field name).

If Rate is always part of the calculation, you can do something like this:

Me.Total_Charges = Me.Rate * IIf(Me.Rate_Type = "hour", Me.Hours, 1)

In this example, IIf is used to determine what multiplier to apply to Rate.

You need to be careful with IIf statements - make sure every variable used can return a value, even for the 'inactive' side. If any field resolves to Null, the whole statement will fail (i.e. generate and error). In the above example, Me.Hours must have a value.

 

[SirStevie3]

What type of boxes are these (Combo, List, Text)? The answer will shape the way to tackle the solution.

They are text boxes. one is currency format the other is just text.

on another note... I LOVE YOU! it worked! now i just have to figure out some other things.

here's what i came up with so far (i substituted an 8 for me.hours just to make sure this part of the code works before i tackle how im going to calclulate hours):

Me.qryViewSessionssubform.Form.Total_Charges = Me.qryViewSessionssubform.Form.Rate * IIf(Me.qryViewSessionssubform.Form.Rate_Type = "hour", 8, 1)

It took me a bit because these controls are on a subform, and the subform gets refreshed by a command button on the main form. I initially tried adding your code to the on load event of the subform, but that didnt work. i was getting an error because when the subform loads its blank until the command button on the main form refreshes it. so i added the code to the end of the refresh button and it works like a charm!

 

[Roku]

I LOVE YOU!

Steady on! It's not that good.

Glad to have helped - and pleased that you took it forward logically.

 

[ppieroudis]

IIf([Programme ID]='EFAW-GR' Or [Programme ID]='EFAW-ENG' Or [Programme ID]='EPFA-GR' Or [Programme ID]='EPFA-ENG' Or [Programme ID]='FAW-ENG' Or [Programme ID]='FAW-GR' Or [Programme ID]='MEDTEC-ENG' Or [Programme ID]='MEDTEC-GR' Or [Programme ID]='MG-ENG' Or [Programme ID]='MG-GR' Or [Programme ID]='PFA-GR' Or [Programme ID]='PFA-ENG' Or [Programme ID]='FAWARC-GR' Or [Programme ID]='FAWARC-ENG',[Date Finish]+1095,IIf([Programme ID]='ARC-GR' Or [Programme ID]='ARC-ENG' Or [Programme ID]='CPR-ENG' Or [Programme ID]='CPR-GR',[Date Finish]+364,Null))

After a loooot of hours i came to this !! it finally works

Thanks everyone!