Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2012
    Posts
    3

    Unanswered: Filter dates using a combo box

    I hope this is straight forward.
    I have a sub form which is in continuous form format. I need to filter the date field based on what I select in an unbound combo box.
    If I want to show records created in the last seven days, I select "7 Days" from the combo and the last seven days records show in the form. If I select "30 days", the last 30 days of records will show. I also want to be able to show ALL records by selecting "ALL" from the combo box. The default value will be "7 Days".
    Which way is the best way to go about this.

    Thanks.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Here's a possibility:
    1. The susbform/subreport control in the parent form is named "Child_SubForm".
    2. The combobox in the parent form is named "Combo_DateRange"
    3. The Date/Time column on which the contents of the subform must be filtered is named "Date_Field"
    4. All the relevant properties of the combo are dynamically assigned by code in the Form_Open event, although most of them could be statically assigned in the Properties window when the form is open in design view.
    Code:
    Private Sub Combo_DateRange_AfterUpdate()
    
        Me.Child_SubForm.Form.Filter = Nz(Me.Combo_DateRange.Column(2), "")
        Me.Child_SubForm.Form.FilterOn = True
    
    End Sub
    
    Private Sub Form_Open(Cancel As Integer)
    
        Const c_SQL As String = "SELECT TOP 1 '0' AS Col0, 'All' AS Col1, '' AS Col2 FROM msysobjects " & _
                                "Union " & _
                                "SELECT TOP 1 '1' AS Col0, '7 days' AS Col1, '[Date_Field] > DateAdd(/d/, -7, Now())' AS Col2 FROM msysobjects " & _
                                "Union " & _
                                "SELECT TOP 1 '2' AS Col0, '30 days' AS Col1, '[Date_Field] > DateAdd(/d/, -30, Now())' AS Col2 FROM msysobjects;"
    
        With Me.Combo_DateRange
            .ColumnCount = 3
            .ColumnWidths = "0;1250;0"
            .BoundColumn = 1
            .DefaultValue = 1
            .LimitToList = True
            .RowSourceType = "Table/Query"
            .RowSource = Replace(c_SQL, "/", Chr(34))
        End With
        Combo_DateRange_AfterUpdate
        
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Mar 2012
    Posts
    3
    Tried that (replacing names of objects as necessary) but the whole subform went blank (design, fields, the lot disappeared).

    I removed the combo properties from the VBA and entered them manually. This solved the "blanking" issue. However all I was getting then was the numbers 0,1,2 in the combo dropdown and no filtering (showed all records all the time). I then removed the OnOpen stuff completely and recreated an SQL query with the SQL stuff in the RowSource property. The query ran OK, showing
    Col0 - 0,1,2
    Col1 - ALL, 7 Days, 30 Days
    Col2 - The formulas - [MyCombo] > DateAdd(/d/, -7, Now()) etc...

    However, the combo is still showing 0,1,2 and no filtering is performed.

    I have kept to the property settings you provide and kept the VBA AfterUpdate stuff unchanged (aside from changing the objects to my own objects).

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you assigned the properties of the combo through the Property window, please be sure that the following properties are as follows:

    ColumnCount = 3
    ColumnWidths = "0;1250;0"
    BoundColumn = 1
    DefaultValue = 1
    LimitToList = True
    RowSourceType = "Table/Query"
    RowSource = Replace(c_SQL, "/", Chr(34))

    Included: a sample database with the original example.
    Attached Files Attached Files
    Have a nice day!

  5. #5
    Join Date
    Mar 2012
    Posts
    3
    Well done that person!

    It was me being a twit. No matter how hard I studied the code I could not figure out why it didn't work. It was when you posted the example I began to realised I was using the wrong date field in the SQL bit of code. Embarassingly I was using the name of the form's date field rather than the table's date field (the name was stuck in my brain so I kept using it )).

    Thank a million. I can now move on.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I know the problem as it placed me in some awkward and embarrassing situations.

    You're welcome!
    Have a nice day!

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    always a good idea to assign SQL to a variable so you can examine what you are sending to the SQL engine is actually what you think it is. whether you show that as a msgbox or stop the code using a break point is up to you.


    and don't worry about it, its soemthing we all do
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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