How To Apply Filter To Form Using VBA

Discussion in 'Microsoft Access Form Coding' started by MikeC, Oct 21, 2004.

  1. MikeC

    MikeC Guest

    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?
     
    MikeC, Oct 21, 2004
    #1
    1. Advertisements

  2. MikeC

    Rex Gillit Guest

    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" <> wrote in message
    news:L2Vdd.17055$...
    > 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?
    >
    >
    >
     
    Rex Gillit, Oct 21, 2004
    #2
    1. Advertisements

  3. MikeC

    MikeC Guest

    That solved it. Thanks.

    "Rex Gillit" <> wrote in message
    news:ZpVdd.8391$...
    > 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" <> wrote in message
    > news:L2Vdd.17055$...
    >> 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?
    >>
    >>
    >>

    >
    >
     
    MikeC, Oct 21, 2004
    #3
  4. MikeC

    qalalah

    Joined:
    Jul 23, 2010
    Messages:
    2
    Likes Received:
    0
    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.
     
    qalalah, Jul 23, 2010
    #4
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    Help: Apply Form Filter using checkbox & linked table

    Guest, Jan 17, 2005, in forum: Microsoft Access Form Coding
    Replies:
    7
    Views:
    320
    Rob Oldfield
    Jan 19, 2005
  2. Guest

    using a form to apply filter on a different form (HOW)

    Guest, Jun 29, 2005, in forum: Microsoft Access Form Coding
    Replies:
    0
    Views:
    213
    Guest
    Jun 29, 2005
  3. Guest

    Using apply filter more than once in a form

    Guest, Aug 23, 2006, in forum: Microsoft Access Form Coding
    Replies:
    0
    Views:
    260
    Guest
    Aug 23, 2006
  4. moshe via AccessMonster.com

    Apply filter to sub form when main form is filtered

    moshe via AccessMonster.com, Jul 18, 2007, in forum: Microsoft Access Form Coding
    Replies:
    1
    Views:
    224
    Guest
    Jul 18, 2007
  5. yanguo
    Replies:
    2
    Views:
    342
    yanguo
    Jan 17, 2008
Loading...

Share This Page