Nested IIF statements with 2 criteria to meet

[DixieThunder24]

I have a report based on a query and in that report I added a text box to be yes or no depending on 1) the number of days to complete and 2) the type of urgency. There's 3 different urgency levels so I was trying to create iif statements based on each one with the corresponding days using the OR operator between them like below:

=IIf([t_Days]<=2 And [Urgency Level]="Expedite (1-2 days)","Yes","No")_
Or IIf([t_Days]<=7 And [Urgency Level]="Normal (5-7 days)","Yes","No")_
Or IIf([t_Days]<=30 And [Urgency Level]="Low (14-30 days)","Yes","No")

Any 1 of those by themselves works fine but I need it based on all of that. So with that said, is there a way to do this? I tried using VBA code, but I think with the type of report I'm running that that wont work. It has a repetitive one line detail section with the data I'm trying to view.

 

[CJ_London]

in your query builder criteria area

in the t_Dates column put
<=2
<=7
<=30

in the UrgencyLevel column put
"Expedite (1-2 days)"
"Normal (5-7 days)"
"Low (14-30 days)"

obviously lining up the relevant comparisons

 

[DixieThunder24]

in your query builder criteria area

in the t_Dates column put
<=2
<=7
<=30

in the UrgencyLevel column put
"Expedite (1-2 days)"
"Normal (5-7 days)"
"Low (14-30 days)"

obviously lining up the relevant comparisons

You said the t_dates Column but I assume you mean the t_Days since thats what I'm trying to use in my iif statement.

But my problem with that is that the t_days control is only on my report and that's where it calculates the number of days between my 2 dates.

If I were to add that to my query then it would only select those records when I need everything based on a different criterion.


Is there a way to hide a control using an iif statement? for instance:

=IIf([t_Days]<=2 And [Urgency Level]="Expedite (1-2 days)","Yes",[Visible]=False)

I get the circular reference error when I do that though.

 

[CJ_London]

yes I meant T_days and sorry I misread your post

Try putting this as an extra column in your query builder

Urgency: ([t_Days]<=2 And [Urgency Level]="Expedite (1-2 days)") Or ([t_Days]<=7 And [Urgency Level]="Normal (5-7 days)")
Or ([t_Days]<=30 And [Urgency Level]="Low (14-30 days)")

This will return a true/false value which you can use to set your button visibility with code

mybutton.visible=urgency

 

[DixieThunder24]

I actually got the iif statement with the "or" to work. My problem the first time was trying to do multiple iif statements with or between them instead of doing all the conditions in the first expression. The code below is doing what I ended up with. Thanks for the help.

=IIf([t_Days]<=2 And [Urgency Level]="Expedite (1-2 days)" Or _
[t_days]<=7 And [Urgency Level]="Normal (5-7 days)" _
Or [t_Days]<=30 And [Urgency Level]="Low (14-30 days)","Yes","No")