Macro Condition for Option Group & Combo Box

[Guest]

On a Print Report Dialog form, I have an Option Group to print reports. The
user selects which report they want to print. Below the option group, I have
a combo box for Division, then another combo box which filters the Region
based on the Division selected in the previous combo box. Currently, I have
a where condition in the macro, for the report selected, that allows the user
to run all divisions by not selecting anything in the combo boxes. Also in
the condition is the selection of Division then Region. I would like to add
the ability to also run just the division (if no region selected), but I am
not sure how to write the condition.

Action: OpenReport
Report Name: rptActual
View: Print Preview
Where Condition: =IIf([Forms]![frmBackupRptDialog]![cboRegion] Is
Null,"","[Region] = Forms![frmBackupRptDialog]![cboRegion]")

 

[Steve Schapel]

Karen,

It would probably be preferable to add the references to your Division
and Region comboboxes to the Criteria of the query that the report is
based on, rather than writing a complex Where Condition into the macro.
But, without testing, I am pretty sure it would be something like this
(as far as I know, it would never be applicable to use an IIf() function
in the way you have)...
Where Condition:
([Region]=[Forms]![frmBackupRptDialog]![cboRegion] Or
[Forms]![frmBackupRptDialog]![cboRegion] Is Null) And
([Division]=[Forms]![frmBackupRptDialog]![cboDivision] Or
[Forms]![frmBackupRptDialog]![cboDivision] Is Null)

 

[Guest]

Thanks, this is what I needed. I got the macro from an Access book that
showed how to create a Print Report Dialog form and to use Option Groups,
List Boxes and macros on the form. I just needed to expand on the concept.

Karen,

It would probably be preferable to add the references to your Division
and Region comboboxes to the Criteria of the query that the report is
based on, rather than writing a complex Where Condition into the macro.
But, without testing, I am pretty sure it would be something like this
(as far as I know, it would never be applicable to use an IIf() function
in the way you have)...
Where Condition:
([Region]=[Forms]![frmBackupRptDialog]![cboRegion] Or
[Forms]![frmBackupRptDialog]![cboRegion] Is Null) And
([Division]=[Forms]![frmBackupRptDialog]![cboDivision] Or
[Forms]![frmBackupRptDialog]![cboDivision] Is Null)

--
Steve Schapel, Microsoft Access MVP

On a Print Report Dialog form, I have an Option Group to print reports. The
user selects which report they want to print. Below the option group, I have
a combo box for Division, then another combo box which filters the Region
based on the Division selected in the previous combo box. Currently, I have
a where condition in the macro, for the report selected, that allows the user
to run all divisions by not selecting anything in the combo boxes. Also in
the condition is the selection of Division then Region. I would like to add
the ability to also run just the division (if no region selected), but I am
not sure how to write the condition.

Action: OpenReport
Report Name: rptActual
View: Print Preview
Where Condition: =IIf([Forms]![frmBackupRptDialog]![cboRegion] Is
Null,"","[Region] = Forms![frmBackupRptDialog]![cboRegion]")

 

[Steve Schapel]

Karen,

The overall concept of what you are doing is good, and I didn't mean to
imply otherwise. I just wanted to point out that putting the criteria
into the query would be easier than using the Where Condition argument
in the macro.

 

[Guest]

Thanks, I agree. It became more involved as I was working on the form.