Option group to filter a form

[Brian]

I am hoping someone can tell me why my code behind my option group doesn't
work? I am trying to filter my form by dates in the [Scheduled_End] field.
Option 1, All work fine, Option 2 continues to show all records, and Option 3
give me a Run-time error '424': Object required.

I have tried a few other things without success so I thought I would ask so
I can learn. Here is my code.

Private Sub Frame123_AfterUpdate()
If Frame123 = 1 Then 'All
Me.FilterOn = False
ElseIf Frame123 = 2 Then 'Past Due
Me.Filter = [Scheduled_End] <= Now()
Me.FilterOn = True
Else: Frame123 = 3 'Not Planned
Me.Filter = [Scheduled_End] Is Null
Me.FilterOn = True
End If
Me.Requery
End Sub

 

[Jeff Boyce]

Brian

I don't use filters much, but it seems to me that your .Filter statement
might be missing delimiters. If you are comparing dates, I thought you
needed to use a date-delimiter (#) ... and are you absolutely certain that
the value in [Scheduled_End] is a date/time value (Now() returns date/time
values), and not just a date?

Have you checked Access HELP for the syntax/examples on .Filter?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Brian]

I did a CDate([Scheduled_End]) already to make sure I was comparing Date to
Date but that did the same thing.

Brian

I don't use filters much, but it seems to me that your .Filter statement
might be missing delimiters. If you are comparing dates, I thought you
needed to use a date-delimiter (#) ... and are you absolutely certain that
the value in [Scheduled_End] is a date/time value (Now() returns date/time
values), and not just a date?

Have you checked Access HELP for the syntax/examples on .Filter?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I am hoping someone can tell me why my code behind my option group doesn't
work? I am trying to filter my form by dates in the [Scheduled_End]
field.
Option 1, All work fine, Option 2 continues to show all records, and
Option 3
give me a Run-time error '424': Object required.

I have tried a few other things without success so I thought I would ask
so
I can learn. Here is my code.

Private Sub Frame123_AfterUpdate()
If Frame123 = 1 Then 'All
Me.FilterOn = False
ElseIf Frame123 = 2 Then 'Past Due
Me.Filter = [Scheduled_End] <= Now()
Me.FilterOn = True
Else: Frame123 = 3 'Not Planned
Me.Filter = [Scheduled_End] Is Null
Me.FilterOn = True
End If
Me.Requery
End Sub

 

[John W. Vinson]

I am hoping someone can tell me why my code behind my option group doesn't
work? I am trying to filter my form by dates in the [Scheduled_End] field.
Option 1, All work fine, Option 2 continues to show all records, and Option 3
give me a Run-time error '424': Object required.

I have tried a few other things without success so I thought I would ask so
I can learn. Here is my code.

Private Sub Frame123_AfterUpdate()
If Frame123 = 1 Then 'All
Me.FilterOn = False
ElseIf Frame123 = 2 Then 'Past Due
Me.Filter = [Scheduled_End] <= Now()
Me.FilterOn = True
Else: Frame123 = 3 'Not Planned
Me.Filter = [Scheduled_End] Is Null
Me.FilterOn = True
End If
Me.Requery
End Sub

As Jeff suggested, the Filter property of a form must be a *text string*. Try


Private Sub Frame123_AfterUpdate()
Select Case Frame123
Case 1 'All
Me.Filter = "" ' empty string
Me.FilterOn = False
Case 2 Then 'Past Due
Me.Filter = "[Scheduled_End] <= Date()"
Me.FilterOn = True
Case 3 'Not Planned
Me.Filter = "[Scheduled_End] Is Null"
Me.FilterOn = True
End If
Me.Requery
End Sub

You may need to put similar code in the form's Current event if Frame123 is a
bound control.

 

[Brian]

Thanks
The Case statement fixed my problem.

I am hoping someone can tell me why my code behind my option group doesn't
work? I am trying to filter my form by dates in the [Scheduled_End] field.
Option 1, All work fine, Option 2 continues to show all records, and Option 3
give me a Run-time error '424': Object required.

I have tried a few other things without success so I thought I would ask so
I can learn. Here is my code.

Private Sub Frame123_AfterUpdate()
If Frame123 = 1 Then 'All
Me.FilterOn = False
ElseIf Frame123 = 2 Then 'Past Due
Me.Filter = [Scheduled_End] <= Now()
Me.FilterOn = True
Else: Frame123 = 3 'Not Planned
Me.Filter = [Scheduled_End] Is Null
Me.FilterOn = True
End If
Me.Requery
End Sub

As Jeff suggested, the Filter property of a form must be a *text string*. Try


Private Sub Frame123_AfterUpdate()
Select Case Frame123
Case 1 'All
Me.Filter = "" ' empty string
Me.FilterOn = False
Case 2 Then 'Past Due
Me.Filter = "[Scheduled_End] <= Date()"
Me.FilterOn = True
Case 3 'Not Planned
Me.Filter = "[Scheduled_End] Is Null"
Me.FilterOn = True
End If
Me.Requery
End Sub

You may need to put similar code in the form's Current event if Frame123 is a
bound control.

 

[John W. Vinson]

Thanks
The Case statement fixed my problem.

Well, the IF's would have worked: it was the quotemarks around the filter
string that fixed it!