Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    32

    Unanswered: Filtering a saved query

    Hi all

    My DB consists of several linked tables, and I have a query which calls data from these tables. Among other things, one column in the table is "date".

    Now, my query itself does not have a built-in filter. What I'd like to do is to pass a filter (via a form) so that users can filter the query result by date, among others. Of course, I could hardcode that into the query, but that would not be very user-friendly. Ideally, this filter's lifetime should expire the moment the user leaves the form.

    Is there any way to do this via VBA?
    -andi_kan
    Financial guy by trade, software tinkering by hobby

    PC : Intel(R) Core(TM) i3-3110M CPU @ 2.40GHz, 4GB RAM
    Windows 7 Professional 32-bit SP1
    MS Office 2010

  2. #2
    Join Date
    May 2010
    Posts
    601
    Quote Originally Posted by andi_kan View Post
    Hi all

    My DB consists of several linked tables, and I have a query which calls data from these tables. Among other things, one column in the table is "date".

    Now, my query itself does not have a built-in filter. What I'd like to do is to pass a filter (via a form) so that users can filter the query result by date, among others. Of course, I could hardcode that into the query, but that would not be very user-friendly. Ideally, this filter's lifetime should expire the moment the user leaves the form.

    Is there any way to do this via VBA?
    You could set the form's filter property.

    You could also set the form's record source to be an SQL statement that selects from the saved query and filters by the date field.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    The attached database has an example of querying records based on a date selected from a form.

    Code:
    Private Sub cmdRunQuery_Click()
    
        If IsNull(Me.cmbDateOrdered.Value) Then
            MsgBox "Choose a date before running the query."
            Exit Sub
        End If
        
        DoCmd.OpenQuery "Query1", acViewNormal, acReadOnly
    
    End Sub
    Filter Criteria for the field Date Ordered:
    Code:
    DateSerial(Year([Forms]![Form1]![cmbDateOrdered]),Month([Forms]![Form1]![cmbDateOrdered]),Day([Forms]![Form1]![cmbDateOrdered]))
    Attached Files Attached Files
    Last edited by JerryDal; 05-05-10 at 13:10. Reason: field name spelling

  4. #4
    Join Date
    Jul 2009
    Posts
    32
    Thanks a lot guys, it's working now. I have (temporarily) used HiTechCoach's method as it's easier, plus it'll be needing much more tampering later on.
    -andi_kan
    Financial guy by trade, software tinkering by hobby

    PC : Intel(R) Core(TM) i3-3110M CPU @ 2.40GHz, 4GB RAM
    Windows 7 Professional 32-bit SP1
    MS Office 2010

Posting Permissions

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