Results 1 to 12 of 12
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filtering (Access 2002)

    I have two filters that I am using on a form, and each works perfectly on their own.

    They are enacted by using a click event on different buttons.

    DoCmd.ApplyFilter , "JWord Like [Enter All or Part of Key Word]"

    DoCmd.ApplyFilter , "JDate >[Enter Date]"

    I would like to add a third to combine the two, so that I get a filter where both criteria are met.

    So far I've tried various combinations but failed to get it to work. The help files don't seem to be much use.

    Can anyone help please.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering (Access 2002)

    If you want to return all records that meet *both* criteria, put AND between the criteria. Try:

    DoCmd.ApplyFilter , "JWord Like [Enter All or Part of Key Word] AND JDate >[Enter Date]"

    (Similarly, if you want to return all records that meet *at least one* of the criteria, put OR between the criteria)

  3. #3
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering (Access 2002)

    Thanks

    I had tried the AND outside the quotes.

    It is obvious after it is explained to me.

    Thanks

    Colin

  4. #4
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering (Access 2002)

    Taking this a stage further, is there a way to avoid keying the "/" in the date.

    The data field in the original table uses an input mask of 99/99/99, to keep keystrokes down, and speed up.

    Ideally I'd like to do the same for this filter.

    I've tried setting the IputMask property, before I apply the filter, but I get a message saying "Run Time error 438, Object doesn't support this property or method"

    Ta

    Colin

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filtering (Access 2002)

    Either they have to key in the slashes in the date or you have to wrap the criteria expression in a format function to get the desired result. You can't use input masks except on fields or controls themselves.
    Charlotte

  6. #6
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering (Access 2002)

    Presumably since there are two parameters in this expression (see below), one string and one date, then wrapping the whole thing in a format is impossible.

    "JWord Like [Enter All or Part of Key Word]& '*' AND JDate >[Enter Date]"

    Thanks anyway.

    Colin

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Filtering (Access 2002)

    No, not the whole thing, that parameter. Unfortunately, I don't think it will work, though. Without the slashes to parse the value, Access simply doesn't know how to interpret a number as a date. If it gets it right, it's through sheer dumb luck. You might be better served by expanded the parameter prompt like this:

    JDate > [Enter Date dd/mm/yy]
    Charlotte

  8. #8
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Filtering (Access 2002)

    I would use two textboxes on the form where the user can enter criteria to be used for filter. Then you could use an input mask for the date criteria textbox. The Apply Filter expression would refer to the value of the textbox (or textboxes). This might simplify things.

  9. #9
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering (Access 2002)

    Yes that worked.

    As the number of users is small, and I can train accordingly, I can live with the reqiuirement to enter 6 digit dates.

    And best of all, this works!

    Thanks


    PS for anyone copying Hans' solution, the code needs an extra ) at the end before the "

  10. #10
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering (Access 2002)

    I'll give this a try as well.

    Thanks

  11. #11
    4 Star Lounger
    Join Date
    Jun 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    492
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering (Access 2002)

    Thanks but this one didn't work.


    Colin

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering (Access 2002)

    I haven't really tested this, but you might try

    "... AND JDate > DateSerial(Right([Enter Date], 2), Left([Enter Date], 2), Mid([Enter Date], 3, 2)"

    This will only work if your users *always* enter the date as a six digit string in the form "mmddyy", like 070802. It'll fail if they omit a leading zero or if they enter the year as 2002.

    Added by HansV (July 8, 15:38 UTC):

    Colin correctly pointed out that I forgot one closing parenthesis, so it should have been

    "... AND JDate > DateSerial(Right([Enter Date], 2), Left([Enter Date], 2), Mid([Enter Date], 3, 2))"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •