Multiple report filter using combo box on form

[Zoe]

I need to be able to filter a report by multiple fields. First by the Cost
Center number and then once that is done - then a further filter by Expense
Account Number.

I currently have a form that successfully filters the report by either Cost
Center, Expense Acct Number or SubAccount using a Option Grp/Combo Box set
up. The user selects one of the 3 fields mentioned which populates the combo
box and then they hit a command button (Filter Report). This is the code I
used.

Dim iFilterBy As Integer
Dim iReportType As Integer
Dim iFilterValue As Integer

If IsNull(cmbFilterBy) Then
MsgBox ("Please select a filter criteria.")
Else
iFilterBy = OptGrpFilterBy 'value from the option group
iFilterValue = cmbFilterBy 'value from the combo box

Select Case iFilterBy

Case 1 'Cost Center
Reports![rptBudget].Filter = "CostCenterID = " & iFilterValue
Case 2 'Expense Account Number
Reports![rptBudget].Filter = "ExpenseAcctNumberID = " &
iFilterValue
Case 3 'Sub Account
Reports![rptBudget].Filter = "SubAcctID = " & iFilterValue
End Select

Reports![rptBudget].FilterOn = True
End If

This works fine but now I want to modify the form to first filter by Cost
Center and then by Expense Account. I am not sure how do to this?

Thanks.

 

[John W. Vinson]

I need to be able to filter a report by multiple fields. First by the Cost
Center number and then once that is done - then a further filter by Expense
Account Number.

I currently have a form that successfully filters the report by either Cost
Center, Expense Acct Number or SubAccount using a Option Grp/Combo Box set
up. The user selects one of the 3 fields mentioned which populates the combo
box and then they hit a command button (Filter Report). This is the code I
used.

Dim iFilterBy As Integer
Dim iReportType As Integer
Dim iFilterValue As Integer

If IsNull(cmbFilterBy) Then
MsgBox ("Please select a filter criteria.")
Else
iFilterBy = OptGrpFilterBy 'value from the option group
iFilterValue = cmbFilterBy 'value from the combo box

Select Case iFilterBy

Case 1 'Cost Center
Reports![rptBudget].Filter = "CostCenterID = " & iFilterValue
Case 2 'Expense Account Number
Reports![rptBudget].Filter = "ExpenseAcctNumberID = " &
iFilterValue
Case 3 'Sub Account
Reports![rptBudget].Filter = "SubAcctID = " & iFilterValue
End Select

Reports![rptBudget].FilterOn = True
End If

This works fine but now I want to modify the form to first filter by Cost
Center and then by Expense Account. I am not sure how do to this?

Thanks.

The filter clause needs to be a valid SQL WHERE clause (without the word
WHERE). This can include multiple criteria, using AND logic, OR logic, lists
of values with an IN() clause - anything you can do in a query.

In this case, try

Reports![rptBudget].Filter = "[CostCenterID] = " & optGrpFilterBy & _
& " AND [ExpenseAcctNumberID] = " & cmbFilterBy

You don't need to "relay" the value from form control to VBA variable to
Filter string unless it helps you document or follow the code; in this case it
might be more confusing than helpful.

John W. Vinson [MVP]

 

[Zoe]

That worked - thanks for your help!

I need to be able to filter a report by multiple fields. First by the Cost
Center number and then once that is done - then a further filter by Expense
Account Number.

I currently have a form that successfully filters the report by either Cost
Center, Expense Acct Number or SubAccount using a Option Grp/Combo Box set
up. The user selects one of the 3 fields mentioned which populates the combo
box and then they hit a command button (Filter Report). This is the code I
used.

Dim iFilterBy As Integer
Dim iReportType As Integer
Dim iFilterValue As Integer

If IsNull(cmbFilterBy) Then
MsgBox ("Please select a filter criteria.")
Else
iFilterBy = OptGrpFilterBy 'value from the option group
iFilterValue = cmbFilterBy 'value from the combo box

Select Case iFilterBy

Case 1 'Cost Center
Reports![rptBudget].Filter = "CostCenterID = " & iFilterValue
Case 2 'Expense Account Number
Reports![rptBudget].Filter = "ExpenseAcctNumberID = " &
iFilterValue
Case 3 'Sub Account
Reports![rptBudget].Filter = "SubAcctID = " & iFilterValue
End Select

Reports![rptBudget].FilterOn = True
End If

This works fine but now I want to modify the form to first filter by Cost
Center and then by Expense Account. I am not sure how do to this?

Thanks.

The filter clause needs to be a valid SQL WHERE clause (without the word
WHERE). This can include multiple criteria, using AND logic, OR logic, lists
of values with an IN() clause - anything you can do in a query.

In this case, try

Reports![rptBudget].Filter = "[CostCenterID] = " & optGrpFilterBy & _
& " AND [ExpenseAcctNumberID] = " & cmbFilterBy

You don't need to "relay" the value from form control to VBA variable to
Filter string unless it helps you document or follow the code; in this case it
might be more confusing than helpful.

John W. Vinson [MVP]