Query with nulls problem

  • Thread starter Thread starter Shyguy
  • Start date Start date
S

Shyguy

I have a query that gets it criteria from three combo boxes.

Once all criteria are selected the results are shown if all three
match, nothing shows if even 1 is not a match.

I have been trying to figure out how to write the criteria, so that if
the criteria is not found in the table the query acts as if there is
no criteria required for that field, and shows all record.

I hope I am explaining the correctly. My mind is fried.

Thanks for any help

JackG
 
Jack,

Not sure if I am understanding you correctly here, but would it do what
you want if you use OR rather than AND, in other words, if you are using
the query design window, you put the criteria for the three fields on
different lines of the query design grid, instead of across the same line?
 
Thnaks for the reply. I can't use or because that would include ALL
records that had any one of the criteria and I want only the records
that have all of the criteria mentioned. The problem is that if one
of the criteria is left blank, nothing shows.

JackG
 
Jack,

Can you please clarify? Are you talking about when the combobox with
the criteria for one of the fields is left blank? This is what you now
seem to be saying, which is different from what your first post seems to
say?
 
As I mentioned in my first post. My brain is fried. :-)

You are corect. If only two criteria (combo boxes) are selected, I
want all records with BOTH criteria included, with the third criteria
acting as an "*".

If only one criteria is selected then all records with that criteria
would show.

I was going to try to limit the contents of each combo box by the
selection in the first, but the combo boxes might be selected randomly
and that seems like it would be complicated.

I guess what I need is a way to say that if the combo box has a
selection then use it as criteria, and if there is no selection then
act as if there is no criteria for that field (show all records).

Thanks again for your time and patience

JackG
 
Jack,

Try either of these in the criteria...

[Forms]![NameOfForm]![NameOfCombobox] Or
[Forms]![NameOfForm]![NameOfCombobox] Is Null
(watch for newsreader wordwrap - all on one line!)

Like Nz([Forms]![NameOfForm]![NameOfCombobox],"*")

If you use the first idea, after you save the query you will see that
Access re-arranges it to suit its own purposes... don't worry about this.
 
Steve,

I can't thank you enough. This was driving me buggie. Actually I
have tried..


Like Nz([Forms]![NameOfForm]![NameOfCombobox],"*")

But without the Link Nz. I think I tried an if statement with the
"8".

Anyway. Thanks so much ;-)

JackG
 
Is there a way to do this with three check boxes? If one is checked
it is used as criteria, if not it isn't used.

Thanks for any help
 
Dear guy:

If you wish to use a check box as a tri-state device (yes/no/ignore)
you may code the criteria like this:

WHERE (SomeColumn = [Forms]![FormName]![CheckBoxName]
OR [Forms]![FormName]![CheckBoxName] IS NULL)

If there are any other criteria, then the parentheses is required.

Can you see logically why this works?

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

[Forms]![NameOfForm]![NameOfCheckbox] Or
[Forms]![NameOfForm]![NameOfCheckbox]=0
 
Back
Top