Generating a Filtered Report by selecting a Month from Combo Box

[Nuke1096]

Hello all. Fairly new to Access, but been making good strides! However, I've hit a snag on how to do something I'd like to add to my current project. Basically what I would like to do is create a Combo Box with each month of the year available to select. When I click on a specific month, I want it to pop open a Report based on my table that is filtered by the month I selected. I have a Date column already created in my base table to be my filtering device.



I've already learned how to use query's and reports a bit. I have a Command Button on my form that launches a report based on a query that only shows data for the current year. Likewise, I created one that only shows data for things with a specific item code using a [Which Item Code?] criteria in my query. So in theory, I could do this by creating 12 different buttons, 12 different query's, and 12 different reports. That seems like overkill though and it would seem there has to be an easier way to do that simply by using a combo box to select the month you want to filter.

Any suggestions are much appreciated.

[pbaldy]

Sounds like this may fit:

http://www.baldyweb.com/wherecondition.htm

[Nuke1096]

I'm a bit confused. Wouldn't I have to create a Report first and based if off a query somehow?

[pbaldy]

Yes, I thought you already had that.

[Nuke1096]

Unfortunately not. I'm not sure what I would need for the criteria to only select the specific month that is selected in the combo box, or how to connect the two together.

[pbaldy]

I'm confused as to what you meant by this then: "I have a Command Button on my form that launches a report based on a query that only shows data for the current year." If you have that, and presuming one of the fields in the report is the date field you mentioned, if the combo contained the appropriate dates it could be used with the above technique to further restrict the report by month.

[Nuke1096]

Right, but I would then need a different Report that is based off a different query using different criteria than the query that is generating the report for Current Year button.

Am I right or wrong with that?

[pbaldy]

Perhaps I don't understand the situation, but I see one report. I'd have a form for the user to select whatever criteria were needed to filter the report; year, month, code, etc. Then use the above technique or a parameter query to open the report. I'd only have multiple reports if the layout was different.

[Nuke1096]

To get a better idea of what I mean, I attached the database.


Nova.zip

[Nuke1096]

I'm not sure if what I did is the most efficient way of doing things. Probably not, since I'm still learning, but basically, what I want is for the Combo Box to work the same way as the above three buttons do. Except I want it to filter by Month (instead of Year, Bus, etc), which would be selected from the list.

[pbaldy]

At a glance, the 3 reports appear to have the same layout and source, just different criteria. If so, like I said I'd have a single report. I'd probably have 2 textboxes for a date range and just let the user enter the desired dates. You can have a combo or textbox for bus and if left blank they get all. You can do a similar thing with the dates to let them get all.

[Nuke1096]

But I'm still confused as to how you would go about linking the values you set in the drop down box to a query. How would the query know I'm selecting "March" for example from the drop down combo box instead of "April"?

[pbaldy]

I would use the textboxes previously mentioned for beginning and ending dates. If you wanted, you could use a combo that pushed dates to those textboxes. To use the combo alone you'd have a hidden column containing the month number 1-12 and use the Month() function on your date field to match. That to me is overly complicated so not the way I'd go.

[Nuke1096]

I think I got it working now! Thanks for all the help and quick responses to all my questions

[pbaldy]

Happy to help!