I have a list I'm filtering with a radio button group. It works for all of my radio button options except the one for null values.
The goal is to build a user management list tool for my client. They will use it to view users assigned to certain designations, and it also needs to show users without a designation.
In my data source, I am filtering on the field = form value (my radio button group). This works perfect for 3 of my 4 radio button options, which filter on values that are present in the field being filtered on, but how do I set up the data source and the 4th radio button option to show records where this field is empty?
I must be doing something wrong. I know I have the data source setup correctly as you've illustrated in your screen cap, but I still cannot get a list of records that have a null value in a certain field.
My data source is using the IN filter, and for the radio button value, I tried using value="". As before, the filtering works for the radio button values that have actual values, but not for the empty (null) ones.
I am not referring to your database connection setup - i understood it is okay.
I am not sure if you make difference between an empty database field and a field set to default value null. You should alter your database table and set the column/field to 'not null'. Then the default value will be jut empty field. NULL is confusing as it actually means - undefined.
The thing is - there are no fields that have null values. There is a members table joined to a site assignments table which associates members with one or more site assignments. My datasource is based on an SQL View that grabs all members and their site assignments. There are many members that are not assigned to a site, so the view yields null values where this happens in the db View.
In this case, I think it would just be easier to create a second report that is designed to just list members with no assignments.
Yes, I did check that. There are no fields in the members table with null values.
The null values are a result of the output of an SQL View I created in the database which uses a left join to show all members and their site assignments stored in other tables. I used a left join because I wanted a db View that showed all members, even if they are not assigned.
Thank you for the offer to help further. However, I had to finish this task for the customer quickly this morning on a deadline, so I just created a second db view for members that weren't assigned to anything.
Filtering records with the data sources for null values still seems really awkward, compared to regular recordsets and sql queries. In this situation though, this example isn't a good test for this because the null values aren't coming from database fields - it's a result of data Views I created in the SQL db.