connecting option button to query criteria

[twalsh]

I have a form attached to a query with a chart of data

I want to have option buttons for things like "yesterday", "last 5 days",
"last month" etc....

when you click one of these option buttons, it should change the criteria of
the query's "Date" field and thus change the line chart....

is this possible???

 

[Klatuu]

Yes, it is possible. I assume your query is the form's record source based
on your post. If it is not, then this will not work for that, it would be
more complex, but what you can do is use the After Update event of the option
group to build a filter string then set the form's filter based on the button
selected.

First, option groups only return an integer value. It is the Option Value
property of the button that is clicked, so you have to associate each buttons
value with it's caption so you get the correct results, but the basic idea
would be:

Dim strFilter As String

Select Case Me.MyOptionGroup
Case Is 1 'All Records
strFilter = vbNullString
Case Is 2 'Yesterday
strFilter = "[SomeDate] = #" & DateAdd("d", -1, Date) & "#"
Case Is 3 'Last 5 days
strFilter = "[SomeDate] >= #" & DateAdd("d", -5, Date) & "#"
Case Is 4 'Last Month
strFilter = "[SomeDate] >= #" & DateAdd("m", -1, Date) & "#"
End Select

Me.Filter = strFilter
Me.FilterOn = strFilter <> vbNullString

 

[twalsh]

I would have this as the "after update" event of each option button?

Yes, it is possible. I assume your query is the form's record source based
on your post. If it is not, then this will not work for that, it would be
more complex, but what you can do is use the After Update event of the option
group to build a filter string then set the form's filter based on the button
selected.

First, option groups only return an integer value. It is the Option Value
property of the button that is clicked, so you have to associate each buttons
value with it's caption so you get the correct results, but the basic idea
would be:

Dim strFilter As String

Select Case Me.MyOptionGroup
Case Is 1 'All Records
strFilter = vbNullString
Case Is 2 'Yesterday
strFilter = "[SomeDate] = #" & DateAdd("d", -1, Date) & "#"
Case Is 3 'Last 5 days
strFilter = "[SomeDate] >= #" & DateAdd("d", -5, Date) & "#"
Case Is 4 'Last Month
strFilter = "[SomeDate] >= #" & DateAdd("m", -1, Date) & "#"
End Select

Me.Filter = strFilter
Me.FilterOn = strFilter <> vbNullString
--
Dave Hargis, Microsoft Access MVP

I have a form attached to a query with a chart of data

I want to have option buttons for things like "yesterday", "last 5 days",
"last month" etc....

when you click one of these option buttons, it should change the criteria of
the query's "Date" field and thus change the line chart....

is this possible???

 

[Klatuu]

No, in the After Update event of the Option Group control.
You don't normally even refer to the buttons themselves.
--
Dave Hargis, Microsoft Access MVP

I would have this as the "after update" event of each option button?

Yes, it is possible. I assume your query is the form's record source based
on your post. If it is not, then this will not work for that, it would be
more complex, but what you can do is use the After Update event of the option
group to build a filter string then set the form's filter based on the button
selected.

First, option groups only return an integer value. It is the Option Value
property of the button that is clicked, so you have to associate each buttons
value with it's caption so you get the correct results, but the basic idea
would be:

Dim strFilter As String

Select Case Me.MyOptionGroup
Case Is 1 'All Records
strFilter = vbNullString
Case Is 2 'Yesterday
strFilter = "[SomeDate] = #" & DateAdd("d", -1, Date) & "#"
Case Is 3 'Last 5 days
strFilter = "[SomeDate] >= #" & DateAdd("d", -5, Date) & "#"
Case Is 4 'Last Month
strFilter = "[SomeDate] >= #" & DateAdd("m", -1, Date) & "#"
End Select

Me.Filter = strFilter
Me.FilterOn = strFilter <> vbNullString
--
Dave Hargis, Microsoft Access MVP

I have a form attached to a query with a chart of data

I want to have option buttons for things like "yesterday", "last 5 days",
"last month" etc....

when you click one of these option buttons, it should change the criteria of
the query's "Date" field and thus change the line chart....

is this possible???