IIf statement with multiple conditions

Steve_T said:

Hi,
I have had a look at other posts on which i have attempted to create a IIf statement in a Query but it will not run. It shows a error message saying about the bracketing or a syntex error.
I would be grateful if someone could please have a look at it and lend some knowledge to me as i am a complete newby to them.

First of all let me show what fields i am using from my Table:

Mon_Hrs (this shows hours being worked as shown on rota)
Mon_Reason (this can be either blank or show a 2 digit absence code)
(PH = Part holiday which is rota hours and break time depending on whether full or part time, other reason codes will mean 0 hours worked that day)

MONDAY_HOURS: IIF(IsNull([Mon_Reason]),[Mon_Hrs],IIf([Mon_Reason] = "PH" And [Mon_Hrs] = 7.5,4,IIf([Mon_Reason] = "PH" And [Mon_Hrs] = 5,2.5,0)))

This is my attempt above and i know it will be either something silly or a complete mess and not workable but i would be grateful is someone could help me.

Thanks

Click to expand...

[COLOR=black][FONT=Verdana]MONDAY_HOURS: [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]II[B][COLOR=red]f[/COLOR][/B](IsNull([Mon_Reason]),[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]       [Mon_Hrs],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]       IIf([B][COLOR=red](([/COLOR][/B][Mon_Reason] = "PH"[B][COLOR=#ff0000])[/COLOR][/B] And [B][COLOR=#ff0000]([/COLOR][/B][Mon_Hrs] = 7.5[COLOR=red][B]))[/B][/COLOR],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]               4,[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]               IIf([B][COLOR=#ff0000](([/COLOR][/B][Mon_Reason] = "PH"[B][COLOR=#ff0000])[/COLOR][/B] And [B][COLOR=#ff0000]([/COLOR][/B][Mon_Hrs] = 5[B][COLOR=#ff0000]))[/COLOR][/B],[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]                       2.5,[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]                       0[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]               )[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]       )[/FONT][/COLOR]

Steve_T said:

Brianwarnock,
Thanks for the statement, i looked and looked at the IIf statement and never saw it once, it was driving me mad. Even split it up to try and find the error. This is another example of the support and understanding i have always received here at AWF. No matter how silly or complex a question is you can guarantee someone will try and help or if not direct you to a thread / link that can.

Click to expand...

raskew said:

Hi -

You also might consider the Switch() function. Saves the formatting hassle, matching parenthesis, etc..

MONDAY_Hours = Switch(IsNull([Mon_Reason]), [Mon_Hrs], [Mon_Reason] = "PH" AND [Mon_Hrs] = 7.5, 4, [Mon_Reason] = "PH" And [Mon_Hrs] = 5, 2.5, True, 0)

Bob

Click to expand...