IIF Statement in Textbox

[Tomfernandez1]

Happy Holidays!

I have a table called TA_Runs with a "Run_ID" field that contain either "Summer" or "Spring". Accordingly, I have created a report which is filtered by either "Spring" or "Summer", and on the report is a text field in the header area that displays the filtered season.



I would like to code the text box to either show the filtered season (Spring or Summer), or if unfiltered (showing both seasons) to display "All Records"..
Here is the expression used in the text box on the report form:
=IIf([RUN_ID]="SUMMER",[RUN_ID],IIf([RUN_ID]="SPRING",[RUN_ID],IIf([RUN_ID]="SUMMER" AND [RUN_ID]="SPRING","ALL RECORDS",[RUN_ID])))

It works if either SUMMER or SPRING is filtered, but when all records are in view, the box displays either SUMMER or SPRING.
Again, I'd like the box to display "ALL RECORDS" when report is unfiltered.

Thanks,

[pbaldy]

I would test what the user entered, presumably on a form, not the data itself. As you're doing it, it's simply looking at the first record.

[June7]

How do you open the report with filter? How does user select category?

I would use VBA. Then could use the OpenArgs argument to pass the report category (Summer, Spring, All Records).

DoCmd.OpenReport "report name", , , , , Me.cbxCategory

Then textbox on report:

=[OpenArgs]

or report textbox can reference form control:

= [Forms]![formname]![cbxCategory]

otherwise, have to do some calcs in report header or footer, like:

=IIf(Sum(IIf(RUN_ID="Summer",1,0))>0 AND Sum(IIf(RUN_ID="Spring",1,0))>0, "ALL RECORDS", IIf(Sum(IIf(RUN_ID="Summer",1,0))>0, "Summer", "Spring"))

[Tomfernandez1]

I placed this code in the header and if works fine: =IIf(Sum(IIf(RUN_ID="Summer",1,0))>0 AND Sum(IIf(RUN_ID="Spring",1,0))>0, "ALL RECORDS", IIf(Sum(IIf(RUN_ID="Summer",1,0))>0, "Summer", "Spring"))

Thanks June7!!

Happy Holidays.

-Tommy