Filter report using combo box

[Tskutnik]

OK easy one for anyone that knows Access.

I have a report named [Commissions]
With a combo box in the header of the report named [Combo105] referencing a tale of dates
The underlying query that [Combo105] will cross-reference is called [MonthEnd]

So the question is:
When [Commissions] is open I want the selection made in [Combo105] to filter the report to only the matching values in the underlying [MonthEnd] field. When [Combo105] is cleared all date records should appear. Upon open [Combo105] is null.

I'm quite sure the answer is a combination of a entered into the criteria for [MonthEnd], and/or some AfterUpdate code associated with [combo105], I can't seem to find it online... not for lack of looking.

I need... simple.. simple... simple. I'm a newbie, but once I get this concept other things will make more sense.

Thank very much for any help. Hopefully this is an easy one.

 

[Ranman256]

A button to open the report will look at the cboBox and filter accordingly......

BtnOpenRpt_click()
If IsNull(cboBox) then 
  Docmd.openReport "rMyReport", acPreview
Else
   Docmd.openReport "rMyReport", acPreview,"[field]=" & me.cboBox
End if
End sub

 

[Tskutnik]

Ranman - thanks but if I read your note right that means I have a button to open the report. I'm trying to filter the report after it is open based on a combo box value that can be dynamically changed and re-filtered.
The body of the code you wrote is probably the same, but maybe it belongs in the AfterUpate event?

 

[sneuberg]

Try

IIf(IsNull(Reports![Commissions]![Combo105]), [MonthEnd], Reports![Commissions]![Combo105])

in the query criteria for MonthEnd. Note that when the combo has no selected value the WHERE clause will effectively be [MonthEnd] = [MonthEnd] which is true for the cases where they are equal which is what you want but Null doesn't not equal Null so records with MonthEnd = Null won't be displayed. If there are case where MonthEnd is null and you need them to be displayed you will need to add that condition to the query too,

 

[Tskutnik]

sneuberg - thanks. Im getting a syntax error with:

Private Sub Combo105_Enter ()

IIf(IsNull(Reports![Commissions]![Combo105]), [MonthEnd], Reports![Commissions]![Combo105])

End Sub

 

[sneuberg]

It goes in the criteria for [MonthEnd] in the query, not in the enter event of the combo.

 

[sneuberg]

Also, you probably need to put

Me.Requery

in the afterupdate of the combo box.

 

[Tskutnik]

Sorry this I still not working. I attached the DB. A few things.

1) I entered the IIF statement in the query. It does not seem to be reading from the [Combo105] value.
2) [Commissions] seems to run in a loop for 10 seconds after a date is entered.
3) Strangely [Combo105] is showing in the report as a text box and not a combo.
4) There is no AfterUpdate event available - sorry I made this mistake in an earlier post. I tried the Requery statement in OnEnter and it runs in a loop.

I really appreciate the help - sorry I cant get this to work and have to keep asking.

 

[JHB]

Most of the things you experience here relates to using a report instead of a form.
A report should be seen it is, a piece of "paper" where it is not possible to change anything once "printed".

 

[Ranman256]

then put the code in the combo AFTERUPDATE

 

[JHB]

then put the code in the combo AFTERUPDATE

In a report there is NO AfterUpDate event.

 

[sneuberg]

Sorry for leading you astray. I didn't know that a combo box doesn't work on a report.

You can get the same effect you want with a continuous form. I created one (not pretty) in the attached database to demonstrate. In the process of putting this together I realized that the expression I gave you wouldn't have worked anyway as the bound field is an Autonumber and only the bound field can be used in a query expression. You can work around this by added a textbox to a form to hold the date but in the attached database I just deleted the Autonumber and made MonthEnd the primary key of List Dates. Also as an aside I suspect the other fields in this table, MonthsDays and YYYYMM could be calculated.

I named the form Commissions and the combo box Combo105 so the just Reports changed to Forms in the query criteria expression. It's now.

IIf(IsNull([Forms]![Commissions]![Combo105]),[MonthEnd],[Forms]![Commissions]![Combo105])

I added a button to the form name Display All which returns the combo box to the state when the form was once, i.e., sets it to null and requeries the form. Since the report still has the same record source as the form it will display the same records as the form. I added an Open Report button to the form just using the wizard.

 

[sneuberg]

Another way you could do this is to create a query of the distinct month end dates that are in the CommissionCalc_Source table, e.g.,

SELECT DISTINCT CommissionCalc_Source.MonthEnd
FROM CommissionCalc_Source;

which I named Combo105RowSource in the attached database. If you use this for the row source of the combo box then it will only have dates in it for which there are records. If you use the List_Dates as a row source the user can pick dates for which there are no records.

 

[Tskutnik]

All - thanks for your input - and sneuberg - for the db samples. This makes more sense now. I'll move the functionality to a form and consider this thread closed.