Report built from union query, accessed from form

[tabitha]

I need to create a census report from the employees we serve and their dependents, by company. I have a union query of the Employees table and Dependents table to put them all on one list, then a form where you can type the name of the company you need, with a button that opens the report. This works great, except for the fact that sometimes we don't need the dependents; if a company has over 100 Full-Time employees, we only need the employees. I've added a check box ([IncDep]) on the form, asking the user if they want to include the dependents, but I don't know how to get this to filter the report. I have a Relationship field on the tables that specifies an employee as a "Subscriber", so I'd like to filter the report where [Relationship] = "Subscriber" but I don't know how to do this. The union query specifies both Employees.Relationship and Dependents.Relationship to the text box [Relationship], but when I do something like

Dim stDocName As String

If Me!IncDep.Value = False Then
stDocName = "Census"
DoCmd.OpenReport stDocName, acViewReport, , "[Relationship]='" & "Subscriber" & "'"
Else
stDocName = "Census"
DoCmd.OpenReport stDocName, acViewReport
End If

It doesn't make any difference, dependents are still there.

Any help would be great, I've been on this for three days...

 

[pbaldy]

Shot in the dark, but try reversing the logic or try the modification below. It may be starting out Null, which would fail the test.

If Nz(Me!IncDep.Value, False) = False Then

 

[tabitha]

Oh my goodness, you're absolutely right, it was considering the checkbox as Null because I hadn't clicked and unclicked it before running the report! This is great, thanks!

Now I just need it to sort properly lol

 

[pbaldy]

Happy to help!