FIlter subform on time-range defined by mainform cboTimeRangeSelec

M

Mishanya

I have Mainform with cboTimeRangeSelector based on tblTimeRange:

RangeID Range RangeExpression
1 Month "Between Date( ) And DateAdd("m", 1, Date( )) "
2 Quarter "Between Date( ) And DateAdd("m", 3, Date( )) "
3 Year "Between Date( ) And DateAdd("m", 12, Date(
)) "
4 ThisYear "Year = Year()"

The Subform is based on tblEventDetails and has EventDate control. It is
linked to the Mainform by ClientID.

I try to filter the output of the subform by the AfterUpdate event of the
cboTimeRangeSelector:

Dim f As Form
Set f = Forms![Mainform]![Subform].Form
f.Filter = "EventDate = " & Me!cboTimeRangeSelector.Column(2)
f.FilterOn = True

and get run-yime error 2448 "You can't assign a value to this object".

1) Is my approach legit at all?
2) If yes, what is the catch?
 
M

Mishanya

Maybe using quotation in the string parameter (Between Date( ) And
DateAdd("m", 1, Date( )) ) was not a good idea. So I've changed the
tblTimeRange to:

RangeID Range RangeExpression
1 Month "Between Date( ) And (Date( )+30)"
2 Quarter "Between Date( ) And (Date( )+90)"
3 Year "Between Date( ) And (Date( )+365)"
4 ThisYear "Year = Year()"

and now I get Error 2101 "You've cancelled the previous operation" and the
line
f.Filter = "EventDate= " & Me!DateRangeSelector.Column(2)
gets yellowed.

Dim f As Form

Set f = Forms![Main]![Sub].Form
f.Filter = "EventDate= " & Me!DateRangeSelector.Column(2)
f.FilterOn = True

Any help?
 
J

John Spencer

Don't know but I suspect you want

f.Filter = "EventDate " & Me.DateRangeSelector.Column(2)
Note the dropping of the equals sign.

and for RangeID 4 an expression like:
Between DateSerial(Year(Date()),1,1) and DateSerial(Year(Date()),12,31)

What you have now would end up as
EventDate = Year=Year()
That makes no sense at all. What you were probably trying to construct
would be
Year(EventDate) = Year(Date())
That would be complex, although you could accomplish it with
" = EventDate and Year(EventDate) = Year(Date())"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Maybe using quotation in the string parameter (Between Date( ) And
DateAdd("m", 1, Date( )) ) was not a good idea. So I've changed the
tblTimeRange to:

RangeID Range RangeExpression
1 Month "Between Date( ) And (Date( )+30)"
2 Quarter "Between Date( ) And (Date( )+90)"
3 Year "Between Date( ) And (Date( )+365)"
4 ThisYear "Year = Year()"

and now I get Error 2101 "You've cancelled the previous operation" and the
line
f.Filter = "EventDate= " & Me!DateRangeSelector.Column(2)
gets yellowed.

Dim f As Form

Set f = Forms![Main]![Sub].Form
f.Filter = "EventDate= " & Me!DateRangeSelector.Column(2)
f.FilterOn = True

Any help?

Mishanya said:
I have Mainform with cboTimeRangeSelector based on tblTimeRange:

RangeID Range RangeExpression
1 Month "Between Date( ) And DateAdd("m", 1, Date( )) "
2 Quarter "Between Date( ) And DateAdd("m", 3, Date( )) "
3 Year "Between Date( ) And DateAdd("m", 12, Date(
)) "
4 ThisYear "Year = Year()"

The Subform is based on tblEventDetails and has EventDate control. It is
linked to the Mainform by ClientID.

I try to filter the output of the subform by the AfterUpdate event of the
cboTimeRangeSelector:

Dim f As Form
Set f = Forms![Mainform]![Subform].Form
f.Filter = "EventDate = " & Me!cboTimeRangeSelector.Column(2)
f.FilterOn = True

and get run-yime error 2448 "You can't assign a value to this object".

1) Is my approach legit at all?
2) If yes, what is the catch?
Click to expand...
Click to expand...
 
M

Mishanya

1)With dropping the equals sign I get back to the run-time error 2448 "You
can't assign a value to this object"
2)How can I assign value to the EventDate criteria of the Subform underlying
query if I drop the "=" operator?

John Spencer said:
Don't know but I suspect you want

f.Filter = "EventDate " & Me.DateRangeSelector.Column(2)
Note the dropping of the equals sign.

and for RangeID 4 an expression like:
Between DateSerial(Year(Date()),1,1) and DateSerial(Year(Date()),12,31)

What you have now would end up as
EventDate = Year=Year()
That makes no sense at all. What you were probably trying to construct
would be
Year(EventDate) = Year(Date())
That would be complex, although you could accomplish it with
" = EventDate and Year(EventDate) = Year(Date())"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Maybe using quotation in the string parameter (Between Date( ) And
DateAdd("m", 1, Date( )) ) was not a good idea. So I've changed the
tblTimeRange to:

RangeID Range RangeExpression
1 Month "Between Date( ) And (Date( )+30)"
2 Quarter "Between Date( ) And (Date( )+90)"
3 Year "Between Date( ) And (Date( )+365)"
4 ThisYear "Year = Year()"

and now I get Error 2101 "You've cancelled the previous operation" and the
line
f.Filter = "EventDate= " & Me!DateRangeSelector.Column(2)
gets yellowed.

Dim f As Form

Set f = Forms![Main]![Sub].Form
f.Filter = "EventDate= " & Me!DateRangeSelector.Column(2)
f.FilterOn = True

Any help?

Mishanya said:
I have Mainform with cboTimeRangeSelector based on tblTimeRange:

RangeID Range RangeExpression
1 Month "Between Date( ) And DateAdd("m", 1, Date( )) "
2 Quarter "Between Date( ) And DateAdd("m", 3, Date( )) "
3 Year "Between Date( ) And DateAdd("m", 12, Date(
)) "
4 ThisYear "Year = Year()"

The Subform is based on tblEventDetails and has EventDate control. It is
linked to the Mainform by ClientID.

I try to filter the output of the subform by the AfterUpdate event of the
cboTimeRangeSelector:

Dim f As Form
Set f = Forms![Mainform]![Subform].Form
f.Filter = "EventDate = " & Me!cboTimeRangeSelector.Column(2)
f.FilterOn = True

and get run-yime error 2448 "You can't assign a value to this object".

1) Is my approach legit at all?
2) If yes, what is the catch?
Click to expand...
Click to expand...
Click to expand...
 
M

Mishanya

OK - I've managed - did not catch that Between does not need "=" operetor.
Just one last touch:
In order to simulate the filter cancellataion I can assign one more value
"ALL" = "between Date() and Date()+100000000" something.
But what is the code to swith off the filter with the "ALL" value of the
DateRangeSelector?

Dim f As Form
Set f = Forms![Main]![Sub].Form
f.Filter = "EventDate " & Me.DateRangeSelector.Column(2)
f.FilterOn = True

John Spencer said:
Don't know but I suspect you want

f.Filter = "EventDate " & Me.DateRangeSelector.Column(2)
Note the dropping of the equals sign.

and for RangeID 4 an expression like:
Between DateSerial(Year(Date()),1,1) and DateSerial(Year(Date()),12,31)

What you have now would end up as
EventDate = Year=Year()
That makes no sense at all. What you were probably trying to construct
would be
Year(EventDate) = Year(Date())
That would be complex, although you could accomplish it with
" = EventDate and Year(EventDate) = Year(Date())"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Maybe using quotation in the string parameter (Between Date( ) And
DateAdd("m", 1, Date( )) ) was not a good idea. So I've changed the
tblTimeRange to:

RangeID Range RangeExpression
1 Month "Between Date( ) And (Date( )+30)"
2 Quarter "Between Date( ) And (Date( )+90)"
3 Year "Between Date( ) And (Date( )+365)"
4 ThisYear "Year = Year()"

and now I get Error 2101 "You've cancelled the previous operation" and the
line
f.Filter = "EventDate= " & Me!DateRangeSelector.Column(2)
gets yellowed.

Dim f As Form

Set f = Forms![Main]![Sub].Form
f.Filter = "EventDate= " & Me!DateRangeSelector.Column(2)
f.FilterOn = True

Any help?

Mishanya said:
I have Mainform with cboTimeRangeSelector based on tblTimeRange:

RangeID Range RangeExpression
1 Month "Between Date( ) And DateAdd("m", 1, Date( )) "
2 Quarter "Between Date( ) And DateAdd("m", 3, Date( )) "
3 Year "Between Date( ) And DateAdd("m", 12, Date(
)) "
4 ThisYear "Year = Year()"

The Subform is based on tblEventDetails and has EventDate control. It is
linked to the Mainform by ClientID.

I try to filter the output of the subform by the AfterUpdate event of the
cboTimeRangeSelector:

Dim f As Form
Set f = Forms![Mainform]![Subform].Form
f.Filter = "EventDate = " & Me!cboTimeRangeSelector.Column(2)
f.FilterOn = True

and get run-yime error 2448 "You can't assign a value to this object".

1) Is my approach legit at all?
2) If yes, what is the catch?
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

How about
If Me.DateRangeSelector.column(2) = "All"
f.FilterOn = False
Else
f.FilterOn = True
End If


Or if you don't want to do that

"Between DateSerial(0,1,1) and DateSerial(4000,12,31)"

That will work as long as the field EventDate has a date in it. If
EventDate can be null, then your best bet is to add the lines above.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

OK - I've managed - did not catch that Between does not need "=" operetor.
Just one last touch:
In order to simulate the filter cancellataion I can assign one more value
"ALL" = "between Date() and Date()+100000000" something.
But what is the code to swith off the filter with the "ALL" value of the
DateRangeSelector?

Dim f As Form
Set f = Forms![Main]![Sub].Form
f.Filter = "EventDate " & Me.DateRangeSelector.Column(2)
f.FilterOn = True

John Spencer said:
Don't know but I suspect you want

f.Filter = "EventDate " & Me.DateRangeSelector.Column(2)
Note the dropping of the equals sign.

and for RangeID 4 an expression like:
Between DateSerial(Year(Date()),1,1) and DateSerial(Year(Date()),12,31)

What you have now would end up as
EventDate = Year=Year()
That makes no sense at all. What you were probably trying to construct
would be
Year(EventDate) = Year(Date())
That would be complex, although you could accomplish it with
" = EventDate and Year(EventDate) = Year(Date())"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Maybe using quotation in the string parameter (Between Date( ) And
DateAdd("m", 1, Date( )) ) was not a good idea. So I've changed the
tblTimeRange to:

RangeID Range RangeExpression
1 Month "Between Date( ) And (Date( )+30)"
2 Quarter "Between Date( ) And (Date( )+90)"
3 Year "Between Date( ) And (Date( )+365)"
4 ThisYear "Year = Year()"

and now I get Error 2101 "You've cancelled the previous operation" and the
line
f.Filter = "EventDate= " & Me!DateRangeSelector.Column(2)
gets yellowed.

Dim f As Form

Set f = Forms![Main]![Sub].Form
f.Filter = "EventDate= " & Me!DateRangeSelector.Column(2)
f.FilterOn = True

Any help?

:

I have Mainform with cboTimeRangeSelector based on tblTimeRange:

RangeID Range RangeExpression
1 Month "Between Date( ) And DateAdd("m", 1, Date( )) "
2 Quarter "Between Date( ) And DateAdd("m", 3, Date( )) "
3 Year "Between Date( ) And DateAdd("m", 12, Date(
)) "
4 ThisYear "Year = Year()"

The Subform is based on tblEventDetails and has EventDate control. It is
linked to the Mainform by ClientID.

I try to filter the output of the subform by the AfterUpdate event of the
cboTimeRangeSelector:

Dim f As Form
Set f = Forms![Mainform]![Subform].Form
f.Filter = "EventDate = " & Me!cboTimeRangeSelector.Column(2)
f.FilterOn = True

and get run-yime error 2448 "You can't assign a value to this object".

1) Is my approach legit at all?
2) If yes, what is the catch?
Click to expand...
Click to expand...
Click to expand...
 
M

Mishanya

Meantime I came up with UndoFilter Button

Private Sub btnUndoFilter_Click()
Dim f As Form
Set f = Forms![Main]![Sub].Form
f.FilterOn = False
End Sub

But Your way is more logical.
Thanks again.

John Spencer said:
How about
If Me.DateRangeSelector.column(2) = "All"
f.FilterOn = False
Else
f.FilterOn = True
End If


Or if you don't want to do that

"Between DateSerial(0,1,1) and DateSerial(4000,12,31)"

That will work as long as the field EventDate has a date in it. If
EventDate can be null, then your best bet is to add the lines above.


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

OK - I've managed - did not catch that Between does not need "=" operetor.
Just one last touch:
In order to simulate the filter cancellataion I can assign one more value
"ALL" = "between Date() and Date()+100000000" something.
But what is the code to swith off the filter with the "ALL" value of the
DateRangeSelector?

Dim f As Form
Set f = Forms![Main]![Sub].Form
f.Filter = "EventDate " & Me.DateRangeSelector.Column(2)
f.FilterOn = True

John Spencer said:
Don't know but I suspect you want

f.Filter = "EventDate " & Me.DateRangeSelector.Column(2)
Note the dropping of the equals sign.

and for RangeID 4 an expression like:
Between DateSerial(Year(Date()),1,1) and DateSerial(Year(Date()),12,31)

What you have now would end up as
EventDate = Year=Year()
That makes no sense at all. What you were probably trying to construct
would be
Year(EventDate) = Year(Date())
That would be complex, although you could accomplish it with
" = EventDate and Year(EventDate) = Year(Date())"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


Mishanya wrote:
Maybe using quotation in the string parameter (Between Date( ) And
DateAdd("m", 1, Date( )) ) was not a good idea. So I've changed the
tblTimeRange to:

RangeID Range RangeExpression
1 Month "Between Date( ) And (Date( )+30)"
2 Quarter "Between Date( ) And (Date( )+90)"
3 Year "Between Date( ) And (Date( )+365)"
4 ThisYear "Year = Year()"

and now I get Error 2101 "You've cancelled the previous operation" and the
line
f.Filter = "EventDate= " & Me!DateRangeSelector.Column(2)
gets yellowed.

Dim f As Form

Set f = Forms![Main]![Sub].Form
f.Filter = "EventDate= " & Me!DateRangeSelector.Column(2)
f.FilterOn = True

Any help?

:

I have Mainform with cboTimeRangeSelector based on tblTimeRange:

RangeID Range RangeExpression
1 Month "Between Date( ) And DateAdd("m", 1, Date( )) "
2 Quarter "Between Date( ) And DateAdd("m", 3, Date( )) "
3 Year "Between Date( ) And DateAdd("m", 12, Date(
)) "
4 ThisYear "Year = Year()"

The Subform is based on tblEventDetails and has EventDate control. It is
linked to the Mainform by ClientID.

I try to filter the output of the subform by the AfterUpdate event of the
cboTimeRangeSelector:

Dim f As Form
Set f = Forms![Mainform]![Subform].Form
f.Filter = "EventDate = " & Me!cboTimeRangeSelector.Column(2)
f.FilterOn = True

and get run-yime error 2448 "You can't assign a value to this object".

1) Is my approach legit at all?
2) If yes, what is the catch?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filter query result without presenting the filtering field 6
GoToControl on mainform from subform 6
QUERY CALCULATION BASED ON DATE RANGE? 4
Between function ignores years, after performing date addition 2
Filter based on date range and a field value 6
Filter Query by Year on a form 13
Set date range and filter results via form/menu for Report Preview 12
Average of one range based on date in one column 2

Top