How To Apply Filter To Form Using VBA

  • Thread starter Thread starter MikeC
  • Start date Start date
M

MikeC

I'm developing a time sheet form in AXP that filters records using two
unbound text boxes to define the upper and lower limits of a date range.
The form itself is bound to a recordsource query. When I attempt to apply
the filter using the below code, the form fails to display any records at
all.

The below code runs in a command button's On Click event procedure.

With Me
!txtWkStart = DateAdd("d", -7, !txtWkStart)
!txtWkEnd = DateAdd("d", -7, !txtWkEnd)
.FilterOn = True
DoCmd.ApplyFilter , "[TMS_DATE] >= " & DateValue(!txtWkStart) & "
And [TMS_DATE] <= " & DateValue(!txtWkEnd)
End With

Below is an actual text string that was passed to the above
DoCmd.ApplyFilter method:

[TMS_DATE] >= 10/18/2004 And [TMS_DATE] <= 10/24/2004

In addition, I have also tried using the below lines immediately after
executing the above code, but the form continues to display zero records:

DoCmd.RunCommand acCmdApplyFilterSort
Me.Requery

No error messages display at all. I have stepped the code through the
debugger and it appears to execute just fine, except for the part where it's
supposed to display my data. ;-)

Also, if I toggle-off the "Apply Filter" button using the built-in tool bar,
then *all* records display.

I just can't seem to filter today. What am I missing?
 
Your dates need to be delimited by the symbol "#" to tell Access that they
are dates. Try this:

DoCmd.ApplyFilter , "[TMS_DATE] >= #" & DateValue(!txtWkStart) & _
"# And [TMS_DATE] <= #" & DateValue(!txtWkEnd) & "#"
 
That solved it. Thanks.

Rex Gillit said:
Your dates need to be delimited by the symbol "#" to tell Access that they
are dates. Try this:

DoCmd.ApplyFilter , "[TMS_DATE] >= #" & DateValue(!txtWkStart) & _
"# And [TMS_DATE] <= #" & DateValue(!txtWkEnd) & "#"

MikeC said:
I'm developing a time sheet form in AXP that filters records using two
unbound text boxes to define the upper and lower limits of a date range.
The form itself is bound to a recordsource query. When I attempt to
apply
the filter using the below code, the form fails to display any records at
all.

The below code runs in a command button's On Click event procedure.

With Me
!txtWkStart = DateAdd("d", -7, !txtWkStart)
!txtWkEnd = DateAdd("d", -7, !txtWkEnd)
.FilterOn = True
DoCmd.ApplyFilter , "[TMS_DATE] >= " & DateValue(!txtWkStart) & "
And [TMS_DATE] <= " & DateValue(!txtWkEnd)
End With

Below is an actual text string that was passed to the above
DoCmd.ApplyFilter method:

[TMS_DATE] >= 10/18/2004 And [TMS_DATE] <= 10/24/2004

In addition, I have also tried using the below lines immediately after
executing the above code, but the form continues to display zero records:

DoCmd.RunCommand acCmdApplyFilterSort
Me.Requery

No error messages display at all. I have stepped the code through the
debugger and it appears to execute just fine, except for the part where it's
supposed to display my data. ;-)

Also, if I toggle-off the "Apply Filter" button using the built-in tool bar,
then *all* records display.

I just can't seem to filter today. What am I missing?
Click to expand...
Click to expand...
 
Filter a subform, from the main form

Does anyone have a module or vba for filtering a form, using a subform's table? The main form is unbound and the subform is using a table as it's recordsource. I want to allow users to filter the subform's data using text box controls. Without using command buttons(I hate command buttons). I was using Allen Browne's AppFindAsUType module, for a bound form that I worked on, but it does not work on an unbound main form with a subform.
 
Back
Top