How to sort a access2007 form from comboBox

macok6 · #1 (**permalink**) 01-18-12, 18:12

i've a database in access 2007, and the main form has different fields, one of them is Date, where is the date of transaction.
the date field is in format dd/mm/yyyy, and i want to sort the entire forms records via a comboBox by year, for example the cbo to have this entries
2010
2011
2012
2013
2014

and when i select from comboBox 2011 in the main form to show all the records that has the year in date field equal to 2011.

hope so much in you, and i hope i'm clear.
regards

Sinndho · #2 (**permalink**) 01-19-12, 03:16

As you describe it, it's a filter, not a sort. You can use this (replace the names of the objects by those in your database):

Code:

Private Sub Combo_Year_AfterUpdate()

    Me.Filter = "Year([Date_Transaction]) = " & Me.Combo_Year.Value
    Me.FilterOn = True

End Sub

Note: If "Date" actually is the name of an object in your database, you should change it because it's a reserved word in Access and you'll have problems because of it, sooner or later. See: Access 2007 reserved words and symbols - Access - Office.com

macok6 · #3 (**permalink**) 01-19-12, 04:26

what about if i want to clear the sort? i mean to display all records, so the value in comboBox to be sth like "All records"?
thank you again

Sinndho · #4 (**permalink**) 01-19-12, 04:41

1. You can have an Option group, radio button or anything like that for filtering/not filtering the form:

Code:

Private Sub Toggle_Filter_AfterUpdate()

    If Me.Toggle_Filter.value = True Then
        Me.FilterOn = True
    Else
        Me.FilterOn = False
    End If
    
End Sub

Or shortly:

Code:

Private Sub Toggle_Filter_AfterUpdate()

    Me.FilterOn = Me.Toggle_Filter.value

End Sub

2. You can add an "All years" entry in the combo:

Code:

Private Sub Combo_Year_AfterUpdate()

    If Me.Combo_Year.Value = "All years" then
        Me.FilterOn = False
    Else
        Me.Filter = "Year([Date_Transaction]) = " & Me.Combo_Year.Value
        Me.FilterOn = True
    End Id

End Sub

macok6 · #5 (**permalink**) 01-19-12, 04:44

The second option is better for me.
during the time i reposted, i tried on my own, and i fixed using the second option u recommend to me.
Thank you, thank you so much.
Regards

Sinndho · #6 (**permalink**) 01-19-12, 10:12

You're welcome!

Internet Services

Web Development

Digital Marketing

Consumer Tech