Help with Nulls

[Guest]

Hello all -- I'm have 2 tables -- one with products and one with categories.
They are joined on a "categoryID" key. In the category table, there are 4
'levels' of categories, as a heirarchy -- ex:

ID LVL1 LVL2 LVL3 LVL4
001 Books Fiction Mystery Medical

I have a form that allows a user to choose values for each level via 4
separate comboboxes, and then passes the values for each combobox into a
query which then returns appropriate results. However, the query only works
when a user chooses values for ALL 4 levels. It returns no results if a user
only chooses values for one, two, or three levels (ie: "Show me all products
that have a Level 1 category of "Books").

My basic query says: return all products where the Level 1 category =
combobox1 AND Level 2 category = combobox 2 AND Level 3 category = combobox3
AND Level 4 Category = combobox4.

I thought that the problem may have stemmed from NULL values being passed
thru in the query criteria if someone didn't choose a Level on the form. So,
I adjusted the query criteria to read IFF(combobox2 IS NULL, "*", combobox2).
However, this still doesn't work if a user neglects to choose a value for
combobox 2.

Any ideas or suggestions?

Thanks greatly in advance!

 

[Guest]

It seems to me you could use the NZ function

Instead of :
IFF(combobox2 IS NULL, "*", combobox2)

you use:
Nz (Combobox2, "*")

At any time, if it is null, will be changed to "*"

But my best suggestion would be to create a form that creates the where
clause dynamicaly.

Take Care
Let me know

Mauricio Silva

 

[Guest]

I have the same problem here: I have 2 comboboxes to set criteria for
selection.
When I use AND function - it works OK if both combos are selected. But it
doesn't work if one of them is Null.
When I use OR function - works OK when one is null but returns wrong values
when both combos are selected.

I tried the Nz suggestion, but it wouldn't work! (

Please can you explain what you mean by saying "to create a form that
creates the where clause dynamicaly"? Or advise any other solution that will
work?

Thank you
Lana

 

[Tom Ellison]

Dear Lana:

If you write the SQL dynamically, you can just omit the criterion when
the user hasn't chosen to use the combo box.

A simpler solution for you may be just to get all the logic into a
static query. If the combo boxes are X and Y and the columns are C1
and C2 respectively, I recommend this:

WHERE (C1 = X OR Nz(X, "") = "") AND (C2 = Y OR NZ(Y, "") = "")

It says, basically, that column C1 must be equal to what is in the
combo box if the combo box is "in use", and the same for the other
combo box and its column.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Guest]

Thank you Tom!

I followed your advice and it worked!!!

Thank you so much!

Lana

 

[Tom Ellison]

You're welcome. I knew it would work. Your appreciation is very
welcome.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts