Query with [form]![control] dependencies to records when values are null

[brharrii]

My form consists of a drop down box (cboCustomer) and a subform. The query behind the subformand looks to the combo box to filter out records based on whichever customer is selected. If no customer is selected then all records should be returned.

Currently it works great with one exception: When no customer is selected, the records that aren't associated with a specific customer (LabelCustomer is Null) aren't being returned.

Does anyone have any idea how to fix this?

Thanks
Bruce

SELECT tblLabels.LabelID, tblLabels.LabelNumber, tblLabels.LabelDesc, tblLabels.LabelPlant, tblLabels.LabelCustomer
FROM tblLabels
WHERE (((tblLabels.LabelCustomer) Like IIf(IsNull([forms]![frmNavigationPane]![cboCustomer]),"*",[forms]![frmNavigationPane]![cboCustomer])));

 

[namliam]

When ever you put a filter on any column any null value will never be anything, i.e. Not true and not False.

If you want to show null values either use a NZ to "fake" an empty value in the where cluase or explicitly include "Or Yourfield is null"

 

[brharrii]

Hi Namliam,

Thanks for your response. I'm not sure if I'm completely clear on where to put the nz or the "your field is Null". My condition currently is:

IIf(IsNull([forms]![frmNavigationPane]![cboCustomer]),"*",[forms]![frmNavigationPane]![cboCustomer])));

the * only returns fields with values, it seems like I need some sort of "* + Null" filter though.

Any thoughts?

 

[namliam]

like I said null is nothing, it doesnt exist, null will never be equal or be not equal to ANYTHING but is null....

your "* + Null" filter is my "OR YOURFIELD IS NULL"
Simply create a "OR" line and put IS NULL in it in the query designer

 

[brharrii]

Hmmm, actually Just kidding, i thought it was working, i seem to still be having troubles though.

Pat - When I use the string you provided it does some strange filtering, but doesn't seem to be returning what I'm looking for. if I just replace the "like" with an "=" i get an error message when I try to save the query that says:

This expression is typed incorrectly, or it is too complex to be evaluated.

I have the query saved as

Namliam - I should have clarified, It didn't occur to me when I was explaining though. Adding the "or" statement like you suggested did add the records that have NULL values in the customer column. The problem now is that they are always returning even after a customer has been selected. once the customer has been selected, I want records with Null customers to be filtered out to reduce clutter for the user.

I'm including a copy of my database if that will help at all.

Pat - the query with the changes you suggested are saved to query1

Nimliam - The query with the changes you suggested are saved behind the form: frmNavigationPane. When you open it up you can select the customer drop down and see what it is and isn't filtering.

After I get this filter figured out for customer my plan is to expand the query to filter by "PLANT" and "ITEM NUMBER" as well. I'm hoping it will be doable to have 3 filters of this type working in conjunction with each other.

Thanks again for both of your help.

 

[JHB]

Change the subform's query to the below, (like Pat suggested):

SELECT tblLabels.LabelID, tblLabels.LabelNumber, tblLabels.LabelDesc, tblLabels.LabelPlant, tblLabels.LabelCustomer, tblPlant.PlantDesc, tblCustomers.CustomerDesc
FROM tblPlant RIGHT JOIN (tblCustomers RIGHT JOIN tblLabels ON tblCustomers.CustomerID = tblLabels.LabelCustomer) ON tblPlant.PlantID = tblLabels.LabelPlant
WHERE tblLabels.LabelCustomer=[forms]![frmNavigationPane]![cboCustomer] OR [forms]![frmNavigationPane]![cboCustomer]) Is Null;

 

[namliam]

I think you should look into this thread, the explenation(s) I posted there and possibly the link too... IMHO Filter by form is far more powerfull than form parameters

http://www.access-programmers.co.uk/forums/showthread.php?p=1300908#post1300908