Combo box to select month, then use it to filter that month for report

[annika05]

Hello
Im newbie to access... can you please help me

I was trying to filter my report using combobox selecting only month and year
i want to do is when i select the month and year on that same combobox the report of all transaction on that month year will generate

can someone help me i really don't know how to make it

Thanks in advance...

 

[gemma-the-husky]

you could have a field in the query that drives the form, that tests records against the combo box on your form.

then a me.requery in the combo box update event should work.

alternatively, you could set a filter in the combo box event. again have a field in the query to evaluate the month

then

filter = "calculated month = " & comboboxvalue
filteron = true


in the first case, your query returns only the subset of records
in the second it returns all records, but filters the form to only show certain records

depends which method you prefer.

 

[annika05]

you could have a field in the query that drives the form, that tests records against the combo box on your form.

then a me.requery in the combo box update event should work.

alternatively, you could set a filter in the combo box event. again have a field in the query to evaluate the month

then

filter = "calculated month = " & comboboxvalue
filteron = true


in the first case, your query returns only the subset of records
in the second it returns all records, but filters the form to only show certain records

depends which method you prefer.

Thanks for your reply..

I have my combobox row source to my query DATE but my format here is mm dd, yyyy

i have these code in my row source so i can only see month year of the dates listed on my query

SELECT DISTINCT Format([qryWEEKLY TITHES].[DATE],"mmmm yyyy") AS [DATE] FROM [qryWEEKLY TITHES];

so far it goes its ok but i don't know how to send the filter if i chose the date and year

i have this code on my command button

DoCmd.OpenReport "REPORT2", acViewPreview, Filter10, Format(DATE, "mmmm yyyy") = """&Me.Filter10&"""""

but still when i open the report it doesn't show anything


can you please help me

here's my file for that
View attachment TITHERS DATABASE.zip

 

[Rx_]

The DB zip was very helpful. My suggestion would be to turn your concept upside down from the date point-of-view. Dates are a little tricky for all of us.
The design currently assumes a 5th week. My guess is that there is not always a 5th week. Lets look at this from a different way.

What day of the week do you consider the week to be for your Church's accounting purposes?
For example: The Catholic have Friday evening mass that counts through Sunday, so collections are based on Friday through Sunday. Another church has Saturday morning services, while others have Sunday morning only.
First, determine what your church considers its collection period. Don't assume it is follows the calendar.

Once this is known, start with a table that contains the Collection Period Begin Date. For a beginner, it might be worth just adding this to the table from now to 2019.
From here, let the computer run a formula to determine if this is week 1, 2, 3, 4, 5.
This would change the form to open on the current year and current month.
From there, the user can change the year / month - the computer creates a dynamic list box that has 1 to 4 or 1 to 5 depending on that combination.

To do this quickly, Excel was used to list all of the Sunday dates since 1/6/2013
Often in accounting, knowing the week 1-52 is useful - that was also added.
Next is the week in the month (1..5) this was accomplished with the formula:
=LOOKUP(7+DAY(A2)-WEEKDAY($A2,2),{0,8,15,22,29},{1,2,3,4,5})
Knowing the numeric month number can be useful in dropdown box
in excel the formula is =MONTH(A2) and for year =YEAR(A2)
These were pulled down for 18 months then imported int Access

Now the DB is starting with a universal table for the time periods needed.
This allows more flexibility for looking up information and having it ready cross referenced.

So, after importing the table - I built a little object in your Report form to give you some ideas.
It will open with a list box for the Sundays. The list box will change based on the controls. The dates with the weeks 1..5 will display. Choose one - to automatically fill in a text box.

If you use this table as the basis for all your reports, it might give a standard frame of reference.
It might be enough to give you some ideas and get you started.

I am going to use this same weekly count on a government permit accounting form.

Be sure and look at the Sample Databases found on this site's forum.
There are some good example that might be useful for your assignment.

 

[annika05]

Thank you for your kind reply

i appreciate the db... it was really useful but.. i want to do is first make a combobox that when i select the month and year and also church name it will report all transaction regardless of weeks just all transactions of that month

The church do have many services it is not only sunday but there is friday and thursday so i just want to know how to determine if i put the date, the computer will recognize what week is it, not jut picking it on the list just like i did on my table...

2nd i want to view my report when i just select a week of that given month
and church

when i tied to use that 2 combobox it seems helpful in week and dates but when i include the 3rdcombobox it doesn't really filter my report andalways that parameter value pops up

4th i want to make a 2 combobox that when i select the church name the 2nd combobox list all the names of person on that church and filter the report according on the selected person

Thank you again and i really appreciate your kind support