Using VBA to filter report, need to include null values

[JoeBruce]

Hopefully my title and question are using "null" correctly. I will also state that I did not develop this code on my own - I do not have the VBA knowledge to do that. I am following this guide from a guy named Martin Green. His tutorial is pretty amazing and I give all credit to him.

I have a report (data set is a query) that displays all my teacher information. There is a command button on the report that opens a form with several list boxes. A user (that's me) can select values from the list boxes and click a command button to apply the selected values as a filter. The code is designed so that if nothing is selected from a certain listbox, it puts "Like '*'" into the filter string, so it will filter that field based on any value.

It works great, except that a couple of my fields don't always contain a value. A teacher can be assigned a grade level and/or a department. Maybe they don't have either one. When there is no value in one of these fields, the teacher does not show up after I filter the report.

Earlier I only had City, Grade and School (no Dept), and started to notice the problem - if a teacher did not have a grade level, they didn't show up after filtering. After adding Dept, now only teachers with both a grade level and a department show up after filtering.

Doing some research, I am wondering if the Nz() function might be a solution, but I'm not sure how to implement it. Essentially, I need something that lets the strGrade and strDept include these "null" values when the Len() function returns 0 (bold in below code).

I'm also wondering if this issue could be solved at the table level? If a value is not selected in one of these fields somehow making it a zero-length string? If you're thinking that too, here's some info: The fields in tblTeachers for GradeLevel and Department are number fields, foreign keys. They are joined to the primary keys for tblGradeLevels and tblDepartments.

Private Sub btnApplyFilter_Click()
    Dim varItem As Variant
    Dim strCity As String
    Dim strGrade As String
    Dim strSchool As String
    Dim strDept As String
    Dim strFilter As String
        
    For Each varItem In Me.lstCity.ItemsSelected
        strCity = strCity & ",'" & Me.lstCity.ItemData(varItem) & "'"
    Next varItem
    If Len(strCity) = 0 Then
        strCity = "Like '*'"
    Else
        strCity = Right(strCity, Len(strCity) - 1)
        strCity = "IN(" & strCity & ")"
    End If
    
    For Each varItem In Me.lstGrade.ItemsSelected
        strGrade = strGrade & ",'" & Me.lstGrade.ItemData(varItem) & "'"
    Next varItem
    [B]If Len(strGrade) = 0 Then
        strGrade = "Like '*'"[/B]
    Else
        strGrade = Right(strGrade, Len(strGrade) - 1)
        strGrade = "IN(" & strGrade & ")"
    End If
    
    For Each varItem In Me.lstSchool.ItemsSelected
        strSchool = strSchool & ",'" & Me.lstSchool.ItemData(varItem) & "'"
    Next varItem
    If Len(strSchool) = 0 Then
        strSchool = "Like '*'"
    Else
        strSchool = Right(strSchool, Len(strSchool) - 1)
        strSchool = "IN(" & strSchool & ")"
    End If
    
    For Each varItem In Me.lstDept.ItemsSelected
        strDept = strDept & ",'" & Me.lstDept.ItemData(varItem) & "'"
    Next varItem
   [B] If Len(strDept) = 0 Then
        strDept = "Like '*'"[/B]
    Else
        strDept = Right(strDept, Len(strDept) - 1)
        strDept = "IN(" & strDept & ")"
    End If
    
    strFilter = "[City] " & strCity & "AND [GradeLevel] " & strGrade & "AND [SchoolName] " & strSchool & "AND [Department] " & strDept
    
    With Reports![rptTeacherInformation]
        .Filter = strFilter
        .FilterOn = True
    End With
End Sub

 

[Galaxiom]

Assuming the rest of your code works, something like:

strFilter = "([City] " & strCity & " OR [City] Is Null)" & "AND  etc

Don't forget the parentheses because AND precedes OR.

 

[sneuberg]

If taken literally I believe Galaxiom's example would result in null values being shown even if when the Len() function returns 0. If this is not what you want I believe you will need to move the fields into the if-then-else structure. So with strDept as an example you would change the if-then-else to something like:

If Len(strGrade) = 0 Then
    strGrade = "([GradeLevel] Is Null Or [GradeLevel] Like '*')"
Else
    strGrade = Right(strGrade, Len(strGrade) - 1)
    strGrade = "[GradeLevel] IN(" & strGrade & ")"
End If

Then below in the final concatenate just plop in strGrade without the field name like:

strFilter = "[City] " & strCity & "AND  & strGrade & "AND [SchoolName] " & strSchool & "AND [Department] " & strDept

You would need to do this for each of the fields where you want null values to be shown.

 

[JoeBruce]

After preliminary testing of both of the above solutions, it seems as if they both work. Galaxiom did have it right, because I want to include the null values when Len() = 0. It seems like sneuberg's solution does the same, just at a different point in the code. However it's happening, thanks to you both.

On a related note, I'm curious about this particular function:

strGrade = Right(strGrade, Len(strGrade) - 1)

I'm pretty sure I understand what it does: it returns "strGrade" starting from one less than the right-most character of the string. But why is it necessary? Is this code making a loop, tacking on each selected item at the end of the string?

 

[sneuberg]

This

strGrade = strGrade & ",'" & Me.lstGrade.ItemData(varItem) & "'"

puts a required comma between the items but it also puts one at the beginning where it would cause problems.

This

strGrade = Right(strGrade, Len(strGrade) - 1)

removes that comma.