Filtering Query with an Option Group

[RII]

Hi everybody. This probably have a simple solution, but I can't find my way out here.

I need my Access 2007 database users to select "Month" or "Quarter" or "Year" in the Option Group in a form and pass that selection to the "RegDate" criteria field (Date field type) of a query.

What I try to accomplish here is to limit the quantity of Reports, because they are a lot and each one of they are needed by Month, by Quarter and by Year. Can you imagine, more than 20 Reports and all of them by month, quarter and year!? This simply is crazy!

I try an OptionGroup named "ReportType" with the 3 options: By Month, By Quarter and By Year and AfterUpdate Macro with SetValue of the criteria of the Query with different values for each option:

For By Month:

[ReportType] = 1, SetValue: Item: all the path to the Query Criteria, Value: Year([RegDate]) = Year(Now()) And Month([RegDate]) = Month(Now())

For By Quarter:

[ReportType] = 2, SetValue: Item: all the path to the Query Criteria, Value:

Year([RegDate]) = Year(Now()) And DatePart("q", [RegDate]) = DatePart("q", Now())

and For By Year:

[ReportType] = 3, SetValue: Item: all the path to the Query Criteria, Value:

Year([RegDate]) = Year(Now())

...Don't work!

Try creating a TexBox named Criteria in the form where is the OptionGroup and make selected option change Criteria according explained up, for month, etc. And make the query to read the criteria value from the texbox in the form....Don't work.

Simply.....I don't know what to do.

So, please if anyone can suggest a solution!

Thank you, thank you a lot in advance!

 

[dhookom]

I would place a couple text boxes on the form that contains the option group. This will allow you to set the date range to anything you want but help set for specific intervals.
[tt][blue]
Name: txtStart
Format: Short Date
Default Value: =DateAdd("d",-Day(Date())+1,Date())

Name: txtEnd
Format: Short Date
Default value: =DateAdd("d",-Day(Date())+1,Date())
[/blue][/tt]
Then use some code in the After Update event of the option group:

Private Sub ReportType_AfterUpdate()
    Select Case Me.ReportType
        Case 1   'month
            Me.txtStart = DateSerial(Year(Date), Month(Date), 1)
            Me.txtEnd = DateSerial(Year(Date), Month(Date) + 1, 0)
        Case 2   'Quarter
            Me.txtStart = DateSerial(Year(Date), (DatePart("Q", Date) - 1) * 3 + 1, 1)
            Me.txtEnd = DateAdd("M", 3, Me.txtStart) - 1
        Case 3   'Year
            Me.txtStart = DateSerial(Year(Date), 1, 1)
            Me.txtEnd = DateSerial(Year(Date), 12, 31)
    End Select
        
End Sub

Set the Criteria under your date field in your report's record source to something like:

Between Forms!frmYourFormName!txtStart And Forms!frmYourFormName!txtEnd

Duane
Hook'D on Access
MS Access MVP

 

[RII]

dhookom:

Man, I don't know how to thank you enough for your answer. Your suggetion work as a clock! Really, was great.

How I don't know another way to thank you for your help, the only thing coming to my mind is point you to a place where you can obtain (download and is free) something that might be useful and hope youy already don't have it.

If you have an LCD display, spending lot of hours in front of it can be harmful to the eyes because of the brilliance. And in place of changing it manually all the time, this tinny application do it for you automatically depending of the time of the day, according to the country/region where you are. You can see and download it here:

http://www.stereopsis.com/flux/

Try it. If you already don't know it, I assure you'll like.

Again, THANK YOU VERY MUCH!

 

[dhookom]

Thanks for the comments.

Duane
Hook'D on Access
MS Access MVP