Filtering with a Textbox Breaks with Blank Entries

[dipique]

I currently have a listbox that displays all the items in a table. It includes a couple queries to other tables as well; it works fine. The SQL is:

SELECT Items.ID, Items.QBItemNumber, MFGList.MFG, Items.Model, Items.ModelNumber, Items.HasUniqueID, Items.IsPhysical, Categories.Category, Categories.SubCategory
FROM MFGList INNER JOIN (Categories INNER JOIN Items ON Categories.ID = Items.Category) ON MFGList.ID = Items.Manufacturer;

I would like a listbox that displays all the items in a table... filtered against some text boxes and comboboxes.

The problem is that when I add criteria (for example, I add the criteria "txtFilterItemNumber.Value" to the Items.ID field), the listbox is empty if the filter is blank. I really only want the filter applied if it has something in it.

So then I tried added an "OR" statement. Here's the SQL for just one field:

SELECT Items.ID
FROM Items
WHERE ID=[txtFilterItemNumber].[Value] OR [txtFilterItemNumber].[Value]="";

Still doesn't work--AND when I apply it all the fields, a million criteria appear in design view and Access crashes.

I assume this has done before; what am I doing wrong?

Dan

[dipique]

Update:

I also tried a variation:

SELECT Items.ID
FROM Items
WHERE ID=[txtFilterItemNumber].[Value] OR [txtFilterItemNumber].[Value]="" OR IsNull([txtFilterItemNumber].[Value]);

This works just fine to display all the records, but now the filter itself isn't working.

[Bob Fitz]

Try something like:
WHERE ID=[txtFilterItemNumber].[Value] OR ID = [txtFilterItemNumber].[Value]="";

[dipique]

I tried this but it just said " ' is not a valid name." I also didn't really follow your logic... it seems like you're doing multiple equivalence?

[Bob Fitz]

Try:
WHERE ID=[txtFilterItemNumber].[Value] OR ID = [txtFilterItemNumber].[Value] Is Null;
and also
WHERE ID=[txtFilterItemNumber] OR ID = [txtFilterItemNumber] Is Null;

[dipique]

Turns out the error I encountered was actually from a different issue. Apparently a line break refused to leave the "Row Source" property of my component and that was causing the weird error.

So I tried your suggestion and it worked, but I would love an explanation since I really have no idea what I'm saying. I think it's:
WHERE (ID= txtFilterItemNumber.Value) OR ((ID = [txtFilterItemNumber].[Value]) Is Null));

Is that correct?

In any case, here's my final SQL that worked:

SELECT Items.ID, Items.QBItemNumber, MFGList.MFG, Items.Model, Items.ModelNumber, Items.HasUniqueID, Items.IsPhysical, Categories.Category, 
Categories.SubCategory
FROM MFGList INNER JOIN (Categories INNER JOIN Items ON Categories.ID = Items.Category) ON MFGList.ID = Items.Manufacturer
WHERE 
(Items.ID=txtFilterItemNumber.Value OR Items.ID=txtFilterItemNumber.Value IS NULL) AND 
(Items.QBItemNumber=txtFilterQB.Value OR Items.QBItemNumber=txtFilterQB.Value IS NULL) AND 
(MFGList.MFG=cboFilterMFG.Value OR MFGList.MFG=cboFilterMFG.Value IS NULL) AND 
(Items.Model=txtFilterModel.Value OR Items.Model=txtFilterModel.Value IS NULL) AND 
(Items.ModelNumber=txtFilterModelNumber.Value OR Items.ModelNumber=txtFilterModelNumber.Value IS NULL) AND 
(Items.HasUniqueID=chkFilterUnique.Value OR Items.HasUniqueID=chkFilterUnique.Value IS NULL) AND 
(Items.IsPhysical=chkFilterPhysical.Value OR Items.IsPhysical=chkFilterPhysical.Value IS NULL) AND 
(Categories.Category=cboFilterCat.Value OR Categories.Category=cboFilterCat.Value IS NULL) AND 
(Categories.SubCategory=cboFilterSubCat.Value OR Categories.SubCategory=cboFilterSubCat.Value IS NULL);

This SQL, incidentally, makes Access go nuts. This is how Access treats it:

Access Is Crazy.zip

When I view the SQL, the processor spikes and Access sits frozen for a solid five minutes. If anybody knows how to talk Access into accepting more easily, I'd appreciate it. Anyways, hopefully this helps somebody out someday. Thanks for your help!

Dan