Combining Filters

PC User

PC User

Registered User.
Local time
Today, 06:36
Joined
Jul 28, 2002
Messages
193
On my form I have several filters that work independently; however, in order to maintain a display of data that I want, these filters need to be combined. Can someone help me build a complete form filter by using these individual filters?

Form Declarations
Code: 
Option Compare Database
Option Explicit

Dim strSelect As String, strFrom As String
Dim strSQL As String, strWhere As String
Dim strOrderBy As String
Sort Rows
Code: 
Private Sub optRowSort_AfterUpdate()
            strSelect = "SELECT q2subJournal.* "
            strFrom = "FROM q2subJournal "
    Select Case optRowSort
        Case 1
            strOrderBy = "ORDER BY " & "TickerId" & " Desc"
        Case 2
            strOrderBy = "ORDER BY " & "TickerId" & " Asc"
     End Select

            strSQL = strSelect & strFrom & strOrderBy
            Me!ctlsubForm.Form.RecordSource = strSQL
            Me!ctlsubForm.Form.Requery
End Sub
Expand or Compress Rows by using two different subforms giving the appearance of expanding and compressing.
Code: 
Private Sub optRowStatus_AfterUpdate()
    Select Case optRowStatus
        Case 1
                Me!ctlsubForm.SourceObject = "f2subExpandedRows"
        Case 2
                Me!ctlsubForm.SourceObject = "f2subCompressedRows"
    End Select
End Sub
Select an account name from a combobox list of accounts.
Code: 
Private Sub cboAccount_AfterUpdate()
       
If IsNull(Me.cboAccount) Then

            strSelect = "SELECT q2subJournal.* "
            strFrom = "FROM q2subJournal "
            strWhere = " "
            strSQL = strSelect & strFrom & strWhere

            Me!ctlsubForm.Form.RecordSource = strSQL
            Me!ctlsubForm.Form.Requery

Else
            strSelect = "SELECT q2subJournal.* "
            strFrom = "FROM q2subJournal "

            strWhere = "WHERE q2subJournal.AccountId = " & Me.cboAccount.Column(0)
            strSQL = strSelect & strFrom & strWhere

            Me!ctlsubForm.Form.RecordSource = strSQL
            Me!ctlsubForm.Form.Requery
End If

End Sub
Select a range of dates to display the account transactions.
Code: 
Private Sub cboDateRange_AfterUpdate()

            strSelect = "SELECT q2subJournal.* "
            strFrom = "FROM q2subJournal "

    If Me.cboDateRange <> "" Then
        If Me.cboDateRange = "Last 30 Days" Then
        strWhere = "WHERE q2subJournal.EnterDate " & "Between " & "#" & Format(DateAdd("d", -30, Now()), "Short Date") & "#" & " And " & "#" & Format(Now(), "Short Date") & "#"

    Else
        If Me.cboDateRange = "Last 1 Year" Then
        strWhere = "WHERE q2subJournal.EnterDate " & "Between " & "#" & Format(DateAdd("y", -1, Now()), "Short Date") & "#" & " And " & "#" & Format(Now(), "Short Date") & "#"
    Else
        If Me.cboDateRange = "Annual Period" Then
        strWhere = "WHERE q2subJournal.EnterDate " & "Between " & "#" & Format(DateAdd("y", -1, Now()), "Short Date") & "#" & " And " & "#" & Format(Now(), "Short Date") & "#"
    Else
        If Me.cboDateRange = "Date Range" Then
        DoCmd.OpenForm "fpopSelectDateRange"
    End If
    End If
    End If
    End If
    End If
            strSQL = strSelect & strFrom & strWhere
            Me!ctlsubForm.Form.RecordSource = strSQL
            Me!ctlsubForm.Form.Requery
  
End Sub
Thanks,
PC
 
Thanks for the info. I think the concept of your example is just adding each filter to get a final filter. Would I be wrong to say that at the end of each function I assign the individual filter a name like: Filter1, Filter2, Filter3, etc. Then place that new combined filter into a seperate function that updates whenever any one of the filters are changed. Such as:
Code: 
Private Function AllFilters()
            strSelect = "SELECT q2subJournal.* "
            strFrom = "FROM q2subJournal "
            strWhere = Filter1 & Filter2 & Filter3
            strSQL = strSelect & strFrom & strWhere

            Me!ctlsubForm.Form.RecordSource = strSQL
            Me!ctlsubForm.Form.Requery
End Function
One of my concerns was that other characters or delineators might need to be included in the combining formula. Do you think this code is the right approach?
 
Well, I would describe it as building a single filter based on all possible inputs. The method you describe could work, but isn't how I would do it. I'd either have a single process behind a button that incorporated the various inputs, like the sample. Another way I've done it is to have a function that did the same thing, and call it from each of the possible control's after update event. That makes it more dynamic, where the records update with every input.
 
I've made another attempt at combining the filters using the "recordsource" method. I've made changes to each of the filter functions to define each filter by removing the "WHERE" statement and placing the "WHERE" statement in this query builder function. Again, I'm still getting errors. If it helps, I can post the other filter functions that I've changed.
Code: 
Private Function BuildFilter()

            strSelect = "SELECT q2subJournal.* "
            strFrom = "FROM q2subJournal "
            strWhere = " "

   If (Not IsNull(Me.cboAccount)) Then
    Call SelectAccount
      strWhere = strWhere & strFilter1 & " And "
   End If

   If (Not IsNull(Me.cboDateRange)) Then
    Call DateRange
      strWhere = strWhere & strFilter2 & " And "
   End If

   ' Check the Row Sorting Criteria
    Call RowSort
      'strWhere = strWhere & "ORDER BY " & strFilter3 & " And "

   If (strWhere <> "") Then
      strWhere = Left$(strWhere, Len(strWhere) - 5)
      strWhere = "WHERE" & strWhere
   End If

            strSQL = strSelect & strFrom & strWhere
Debug.Print strSQL
            Me!ctlsubForm.Form.RecordSource = strSQL
            Me!ctlsubForm.Form.Requery

End Function
I get this error message when I try to change the account.
attachment.php
And this is the error highlighted in the module.

attachment.php
 

Attachments

  • Error 3075.jpg
    Error 3075.jpg
    16.9 KB · Views: 313
  • Module With Error.jpg
    Module With Error.jpg
    81.3 KB · Views: 286
Well in your immediate view you have a 'And' in front of the 'Order By'. You cannot have that, you will have to make your code more intelligent so as not to add a 'And' at the end of the where clause. If I am buidling a multilple selection where clause, I add a 'And' at the beginning of the next selection (of course you will have to check to see if the strWhere is non blank).

I hope this helps.
 
Ok. I thank both of you guys for your efforts to help me. I finally did resolve the problem and I'm posting it in its entirety, since I've designed it as seperate functions.

Form Declarations:
Code: 
Option Compare Database
Option Explicit

Dim strSelect As String, strFrom As String
Dim strSQL As String, strWhere As String
Dim strOrderBy As String, strFilter As String
Dim strFilter1, strFilter2, strFilter3 As String

Select an account name from a combobox list of accounts:
Code: 
Private Function SelectAccount()
        strFilter1 = ""
    If IsNull(Me.cboAccount) Then
                strFilter1 = ""
    Else
                strFilter1 = "AccountId = " & Me.cboAccount.Column(0)
    End If
End Function

Select a range of dates to display the account transactions:
Code: 
Private Function DateRange()
        strFilter2 = ""
    If Me.cboDateRange <> "" Then
        
        If Me.cboDateRange = "Last 30 Days" Then
        strFilter2 = "EnterDate " & "Between " & "#" & Format(DateAdd("d", -30, Now()), "Short Date") & "#" & " And " & "#" & Format(Now(), "Short Date") & "#"
        End If
        
        If Me.cboDateRange = "Last 1 Year" Then
        strFilter2 = "EnterDate " & "Between " & "#" & Format(DateAdd("y", -1, Now()), "Short Date") & "#" & " And " & "#" & Format(Now(), "Short Date") & "#"
        End If
        
        If Me.cboDateRange = "Annual Period" Then
        strFilter2 = "EnterDate " & "Between " & "#" & Format(DateAdd("y", -1, Now()), "Short Date") & "#" & " And " & "#" & Format(Now(), "Short Date") & "#"
        End If
    End If     
End Function

Sort Rows:
Code: 
Private Function RowSort()
        strFilter3 = ""
    Select Case optRowSort
        Case 1
            strFilter3 = "TickerId" & " Desc"
         Case 2
            strFilter3 = "TickerId" & " Asc"
     End Select
End Function

Build the filter by combining all the filter functions:
Code: 
Private Function BuildFilter()
            
            strSelect = "SELECT selTrade.* "
            strFrom = "FROM selTrade "
            strWhere = ""
            strFilter = ""
            
   If (Not IsNull(Me.cboAccount)) Then
    Call SelectAccount
      strFilter = strFilter & strFilter1 & " And "
   End If

   If (Not IsNull(Me.cboDateRange)) Then
    Call DateRange
      strFilter = strFilter & strFilter2 & " And "
   End If
   
   strWhere = "WHERE " & strFilter
   
   ' Remove trailing text
   If (strWhere <> "") Then
      strWhere = Left$(strWhere, Len(strWhere) - 4)
   End If
   
   ' Check the Row Sorting Criteria
    Call RowSort
      strOrderBy = "ORDER BY " & strFilter3
 
            strSQL = strSelect & strFrom & strWhere & strOrderBy & ";"
                         Debug.Print strSQL
            Me!ctlsubForm.Form.RecordSource = strSQL
            Me!ctlsubForm.Form.Requery
 
End Function

I hope this can help others who select this design method.

Regards,
PC
 
You must log in or register to reply here.

Similar threads

W
How to Populate a Control in Code and Make it Behave As Though Populated in the GUI
Replies
10
Views
495
arnelgp
arnelgp
W
The problem has been resolved and the program can now be executed. Thank you very much.
Replies
6
Views
1,122
Ken Sheridan
Ken Sheridan
N
Solved How to update bulk selling prices at one go
Replies
5
Views
342
nector
N
C
Matching vba recordset to form recordset
Replies
12
Views
687
Gasman
Gasman
J
Debugger complains about [apparently] non-existant characters
Replies
9
Views
916
The_Doc_Man
The_Doc_Man

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom