Filter a Single Form - Access TechHelp


	Back to Access TechHelp Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Filter a Single Form

James Hopkins

3 years ago

Hey Rickard and Guys, I am trying to figure out how to Filter Dates like: "Today, Last 7 Days, and Last Month", using a Combo Box. I have Continuous Forms on a Single Form. What is the best way to get this done?

	James Hopkins OP @Reply 3 years ago

	Kevin Robertson @Reply 3 years ago
For this I would build an SQL string. When you select an item in the Combo Box it changes the WHERE condition. Check out the Extended Cut of Search Form 2.0. This shows a good example and you should be able to easily modify the code for your needs.

	James Hopkins OP @Reply 3 years ago

	James Hopkins OP @Reply 3 years ago

	James Hopkins OP @Reply 3 years ago

	James Hopkins OP @Reply 3 years ago
Kelvin, I tried something like this. ..

Kevin Robertson @Reply

3 years ago

For a static list of options like you have you don't need the min/max date fields. I would probably write it something like this:

Dim mySQL As String, whereStr As String



mySQL= "SELECT * FROM HoursLogT "

whereStr = ""



If Not IsNull(cboDateFilter) Then

Select Case cboDateFilter

Case "Today"

whereStr = "LogHourDate=#" & Date & "# "

Case "Last 7 Days"

whereStr = "LogHourDate>=#" & Date - 7 & "# And LogHourDate<=#" & Date & "# "

'enter your other options in the same way

End Select

End If



If whereStr <> "" then whereStr = " WHERE " & whereStr



mySQL = mySQL & whereStr

mySQL = mySQL & "ORDER BY LogHourDate"

Me.RecordSource = mySQL

In the code in your images you are trying to set the sort to the value of the selected item in the Combo Box.
This won't work as the ORDER BY need to be a field in the Table or Query.

James Hopkins OP @Reply

3 years ago

Kevin, this is what I have come up with:

Private Sub RequeryForm()

    Dim mySQL As String, whereStr As String

    mySQL = "SELECT * FROM HoursLogT "
    whereStr = ""

    If Not IsNull(CboDateFilter) Then
        Select Case CboDateFilter
            Case "Today"
                whereStr = "LogHourDate=#" & Date & "# "
            Case "Last 7 Days"
                whereStr = "LogHourDate>=#" & Date - 7 & "# And LogHourDate<=#" & Date & "# "
            Case "Last 14 Days"
                whereStr = "LogHourDate>=#" & Date - 14 & "# And LogHourDate<=#" & Date & "# "
            Case "Last 30 Days"
                whereStr = "LogHourDate>=#" & Date - 30 & "# And LogHourDate<=#" & Date & "# "
            Case "Last 60 Days"
                whereStr = "LogHourDate>=#" & Date - 60 & "# And LogHourDate<=#" & Date & "# "
            Case "Last 90 Days"
                whereStr = "LogHourDate>=#" & Date - 90 & "# And LogHourDate<=#" & Date & "# "
            Case "Last Month"
                whereStr = "Month(LogHourDate)=#" & Date - 30 & "# "
            Case "This Month"
                whereStr = "Month(LogHourDate)=#" & Date & "# "
        End Select
    End If

    If whereStr <> "" Then whereStr = " WHERE " & whereStr

    mySQL = mySQL & whereStr
    mySQL = mySQL & "ORDER BY LogHourDate"
    Me.RecordSource = mySQL

End Sub

Also, do I need to "Requery" each Continuous Form?

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access TechHelp.

Next Unseen

New Feature: Comment Live View