Like Operator - run query based on a combo selection

  • Thread starter Thread starter JK
  • Start date Start date
J

JK

I have a form with a combo on it. The combo is a category field. I want to be
able to select a category in the combo and then display all of the records in
the table that have a matching category. I would also like to be able to
select all. So, I have an option group with a two options. One is for all and
the other is for the combo. If all is selected, then a * is inserted into the
field that the query points to.

My problem is this. If I select all, and the * is inserted, the query only
pulls records where the category field is NOT Null or "". If I select all, I
want all.

Any idea how I can work around this?

Thx much!

Jason
 
By using "Like" and "*", you're telling Access to find "something" in that
field. Null is "nothing is there", so you won't find it. I'm not as
certain on the zero-length string ...

Another approach might be to dynamically create a SQL string in code behind
that form. When the <All> option is chosen, there's NO WHERE CLAUSE. When
the <Select one> option is chosen, after a selection, your WHERE clause uses
an exact match, not a "Like", since you picked from exact categories
available.

You'd need a way to "trigger" the query/SQL ... a <Run the query> command
button would work, or you could use the AfterUpdate event of the option
group (if <All>) or the AfterUpdate event of the combobox.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Interesting idea - I'll give it a shot. I'm surprised there isn't an easy fix
to this; seems like it would be a common problem. Anyway, I appreciate your
help!

Jason
 
That's an important observation, Jason.
Null never matches anything: not even a wildcard.

You can craft the WHERE clause to return True where the combo is Null.
For example, if you currently have a SQL statement that includes:
WHERE Category = [Forms].[Form1].[Combo0]
try:
WHERE (([Forms].[Form1].[Combo0] Is Null)
OR (Category = [Forms].[Form1].[Combo0]))

If the combo is Null, the expression will return True.
If the combo is not null, it returns True only if there is a match.
This gives the desired result for each record.
 
Back
Top