Multiple Column Filter

[Elwood07]

Hi,



I'm trying to get VBA to filter by 2 columns, and I cannot get the "ParentDepartment" and "PTODate" to filter together. My code is below.

Public Sub LoadArray()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsFiltered As DAO.Recordset
Dim strSQL As String


strSQL = "SELECT UCase(Left([Associate],InStr([Associate],',')-1)) & '-' & [Department] AS NameDept, qryVacDetail.PTODate, qryVacDetail.ParentDepartment " _
        & "FROM qryVacDetail " _
        & "GROUP BY UCase(Left([Associate],InStr([Associate],',')-1)) & '-' & [Department], qryVacDetail.PTODate, qryVacDetail.ParentDepartment;"
    
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
Dim strFilter As String


With rs
    Debug.Print .RecordCount
    .Filter = "[ParentDepartment]='ROOMS'"
    .Filter = "[PTODate]=42469"
       
    Set rsFiltered = .OpenRecordset
    If Not rsFiltered.BOF And Not rsFiltered.EOF Then
        rsFiltered.MoveLast
        rsFiltered.MoveFirst
            Debug.Print rsFiltered.RecordCount
    End If
    .Close
End With

I've tried

.Filter = "[ParentDepartment]='ROOMS'" And "[PTODate]=42469"

, and multiple variations thereof but always I get a run-timer 13 type mismatch.
They each work fine on their own.
Any ideas?

Thanks

[Elwood07]

Ultimately, I need to replace the "ROOMS" with a reference to the combo box on the form in question, which is cboDept. I would appreciate it you could help with the syntax for that as well.

Many thanks.

[CJ_London]

you can only have one filter at a time

your second example is close, but no banana , try

.Filter = "[ParentDepartment]='ROOMS' And [PTODate]=42469"

I presume that PTODate is a date type, not tried but if it works on its own as a number then fine, if not, use ...=#05/04/2016# - the date needs to be in US format mm/dd/yyyy

for your combo option it would be

.Filter = "[ParentDepartment]='" & cboDept & "' And [PTODate]=42469"

couple of other pointers - you are not summing or counting anything in your query so use SELECT DISTINCT rather than group by

finally - why not just incorporate your filter as criteria in the sql - less code, less data to bring across the network so faster operation

[orange]

What exactly is qryVacDetail?

What is the form involved? Is it a Form/subform construct?

There is a filter example in the sample database at this link
The thread shows a dialog related to filter.
The sample database is a mock up to show the concept and code involved.

Note: I just saw that Ajax has posted while I was typing. I agree with ajax's comments.
I was just going to get you to a sample you could dissect and "work with".

[Elwood07]

Thank you both Ajax and Orange.
The database is for employees to enter their vacation dates. They enter their To/From Dates and the qryVacDetail is a query that pulls off the vacation date entry table and some other cost center and department tables and lists all the days of their vacation within the To/From range.

The form that I'm working on is a calendar, where the user can choose the month, year and department to view planned vacation dates.
Every piece of the form works and populates correctly. And now with your help on the filter syntax is also filters by department but only when it's hardcoded, ie "ROOMS". when I use cboDept I get no returns, but also no errors. The calendar is just blank.

The code that I pasted yesterday was my test module. The following is where I'm at now. You'll notice midway down the rs.filter lines. The first one works fine, but when I replace with cboDept it goes blank. I'm adapting this from existing code and while I can fairly understand what it says, I don't know how to adjust.

As for the GroupBy...i've been going back and forth on the query with summing actual vacation hours and not, so that GROUP BY was a leftover. I'll clean up once I decide if hours will be making a comeback. Thanks for the tip.

Again, I appreciate your help.

Private Sub LoadArray()

'This procedure loads data into the calendar based on date and department filters
'Code based on AccessAllInOne Calendar Tutorial


Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsFiltered As DAO.Recordset
Dim strSQL As String
Dim i As Integer




strSQL = "SELECT UCase(Left([Associate],InStr([Associate],',')-1)) & '-' & [Department] AS NameDept, qryVacDetail.PTODate, qryVacDetail.ParentDepartment " _
        & "FROM qryVacDetail " _
        & "GROUP BY UCase(Left([Associate],InStr([Associate],',')-1)) & '-' & [Department], qryVacDetail.PTODate, qryVacDetail.ParentDepartment;"
    
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)


    'This line ensures the recordset is populated
    If Not rs.BOF And Not rs.EOF Then
        
        'Loops through the array using dates for the filter
        For i = LBound(MyArray) To UBound(MyArray)
        
            'Checks whether the 2nd element of the array = true
             If MyArray(i, 1) Then
               'filter recorddset with array dates
                 'rs.Filter = "[ParentDepartment] = 'ROOMS' AND [PTODate]=" & MyArray(i, 0)
                 rs.Filter = "[ParentDepartment] = '" & cboDept & "' AND [PTODate]=" & MyArray(i, 0)
                                 
                'open up new recordset based on filter
                Set rsFiltered = rs.OpenRecordset
                
                'Loop through new recordset
                Do While (Not rsFiltered.EOF)
                    
                    'Adds text to the 3rd column of the array
                    MyArray(i, 2) = MyArray(i, 2) & vbNewLine _
                    & rsFiltered!NameDept


                    rsFiltered.MoveNext
                Loop
            End If
            'Debug.Print rsFiltered.RecordCount
            'Debug.Print MyArray(i, 2)
        Next i
        
    End If
    
    rsFiltered.Close
    rs.Close
    
'Sets objects to nothing
Set rsFiltered = Nothing
Set rs = Nothing
Set db = Nothing


End Sub

[Elwood07]

aha! my bound column in my Dept combo box was to the dept ID number. Changed the bound column to 2 and it works!