Results 1 to 5 of 5
  1. #1
    breezett93 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    53

    Problems applying a second filter in addition to the original filter

    I have a form that displays data from a query within a date range provided by the user. That date range is the first filter.



    Here is the first filter:
    Code:
    "[Actual Date] BETWEEN #" & Forms![frmMyForm]![txtFrom] & "#  AND #" & Forms![frmMyForm]![txtTo] & "#"
    Once the data is filtered and displayed on the form, I'd like for the user to then click a button which would apply a second filter and display the remaining data.

    Two of the pieces of information that query pulls are dates. I'd like that second filter to be "[Actual Date] > [Promised Date]". The problem is that when I click the button, instead of looking at the data already available, I am getting prompted to enter [Promised Date]. Just like the first filter, it should go row by row and determine whether [Actual Date] > [Promised Date].

    I feel like this should be simple, especially since I have the first filter working properly.

    Thanks

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I NEVER use dynamic parameterized queries. Is that what you are doing?

    Apply both filter criteria together.

    Review http://www.allenbrowne.com/ser-62.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    breezett93 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    53
    I don't want both criteria being applied at the same time. The first criteria needs to be the date-range (this is working just fine). Then, when a button is clicked, additional data needs to be filtered out.

    Would a subform allow this to work?

    I tried using a query, but I was getting prompted to enter a value when the query should have been pulling that info from the form.

  4. #4
    cbende2's Avatar
    cbende2 is offline Competent Performer
    Windows 7 32bit Access 2013
    Join Date
    Jun 2014
    Location
    Louisiana
    Posts
    370
    In your code, create another string that pulls the data for the "new" filter once the button is clicked, and then join the "old" filter with the "new" filter using an AND statement.
    Code:
    Filter1 = "[Actual Date] BETWEEN #" & Forms![frmMyForm]![txtFrom] & "#  AND #" & Forms![frmMyForm]![txtTo] & "#";
    IF MyButtonClickisClicked{
               Filter2 = [Actual Date] > [Promised Date]
               Filter3 = Filter1 AND Filter2;
               ... Apply Filter3
    }
    
    Get my jist?

    I don't have much vb experience. But I'm pretty sure going that route would work.

  5. #5
    breezett93 is offline Advanced Beginner
    Windows 7 64bit Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    53
    Thank you! That worked!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Applying Filter to Form by Defeault
    By matechik4 in forum Programming
    Replies: 7
    Last Post: 08-22-2016, 04:52 PM
  2. Applying a filter in a form
    By 2410Davie in forum Forms
    Replies: 1
    Last Post: 03-07-2016, 03:35 PM
  3. Applying a filter to a report via a button
    By MowsMek in forum Reports
    Replies: 6
    Last Post: 03-16-2015, 01:04 AM
  4. Apply Filter 2 criteria not applying
    By ahightower in forum Forms
    Replies: 4
    Last Post: 07-28-2011, 03:31 PM
  5. Applying a filter to a combo box
    By bugchaser in forum Programming
    Replies: 1
    Last Post: 02-20-2009, 02:37 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums