Report Date Label Based on Form Controls?

[misscrf]

I have form controls that allow a user to choose the following filters for a report:

+ check box for year to date
+ combo box to choose a month/year
+ text box to enter a date to be greater than or equal to
+ text box to enter a date to be less than or equal to
+ use both above text boxes for a range.

It all works fine, but now I want a label to dynamically show the date based on the one chosen in the form.

I tried to make an unbound text box control on the report and put this:

=IIf([Forms]![frmCarSummaryRpt]![ChkYearToDate]=-1,"Year to Date",

IIf([Forms]![frmCarSummaryRpt]![cboMonthYear] Is Not Null,[Forms]![frmCarSummaryRpt]![cboMonthYear],

IIf([Forms]![frmCarSummaryRpt]![dtRangeBegin] Is Not Null And [Forms]![frmCarSummaryRpt]![dtRangeEnd] Is Null,[Forms]![frmCarSummaryRpt]![dtRangeBegin] & " and Later",

IIf([Forms]![frmCarSummaryRpt]![dtRangeBegin] Is Null And [Forms]![frmCarSummaryRpt]![dtRangeEnd] Is Not Null,[Forms]![frmCarSummaryRpt]![dtRangeEnd] & " and Earlier",

IIf([Forms]![frmCarSummaryRpt]![dtRangeBegin] Is Not Null And [Forms]![frmCarSummaryRpt]![dtRangeEnd] Is Not Null,[Forms]![frmCarSummaryRpt]![dtRangeBegin] & " - " & [Forms]![frmCarSummaryRpt]![dtRangeEnd],

"No Date Filter")))))

I doesn't seem to work. No error, just doesn't show the labels I am trying to show. Anyone know how to do this, please?

Thanks!



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

I would probably add a text box (possibly invisible) on the form that is valued with a little code rather than a complex IIf() statement. Then your report can reference the hidden text box value.

Duane
Hook'D on Access
MS Access MVP

 

[misscrf]

Should the code be behind the form or the report? I tried working with openargs, but I think I am missing something in how openargs works.



misscrf

It is never too late to become what you could have been ~ George Eliot

 

[dhookom]

The code would be in the form. I would write a function and call it in the after update of each control or in the code that opens the report.

You could also send the correct string in the OpenArgs.

Duane
Hook'D on Access
MS Access MVP