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.
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.
Code:
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