Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

shanl

Technical User
Apr 2, 2007
41
US
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 ))
 
IIf(([zID]="C" AND [Type]="Leave",[Hrs],0) OR Iif([zID] = "C" AND [TYPE] ="DayOff",[snWkHoursADay],0 ))

How about:

Code:
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.
 
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))
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top