How do you calculate two different criterias into one query field?

[shanl]

I am writing an Access Report where I need to calculate fields in the query that feeds the report. The query references SQL Server tables. Is there a way to calculate two If statements in one calculated results field? This is what I had tried without luck. If a person has taken a whole day of comp time I get the number of hours taken from one field but if a person takes less than a full day of comp time, the value is stored is another field.

Comp: IIf(([zID]="C" AND [Type]="Leave",[Hrs],0) OR Iif([zID] = "C" AND [TYPE] ="DayOff",[snWkHoursADay],0 ))

 

[CaptainD]

IIf(([zID]="C" AND [Type]="Leave",[Hrs],0) OR Iif([zID] = "C" AND [TYPE] ="DayOff",[snWkHoursADay],0 ))

How about:

Iif(([zID] = "C", Iif([Type] = "Leave",[Hrs],Iif([Type] = "DaysOff",[snWkHoursADay],0),0))

not tested, just nesting the Iif's the way I think they should flow.

 

[shanl]

CaptainD,
Thanks so much for the ideas. I tried for a couple hours to get your suggestion to work but kept getting argument errors. I wasn't sure I could even put two IIF together so thanks for confirming that. I utilized your suggestion of putting the default at the end and tried to simplify it as I knew that no matter what I wanted the record if zID=C so I just looked at the two fields and used the one with values. I finally got this combination to work.

Comp: IIf(([zID]="C" And [Hrs]>0),[Hrs],IIf([zID]="C" And [snWkHoursADay]>0,[snWkHoursADay],0))