Multiple search Criteria

  • Thread starter Thread starter Gary S
  • Start date Start date
G

Gary S

Hi,

I have a query that is referencing a form that has multiple combo boxes. I
want this query to use each of the combo boxes as selection criteria. The
problem I am having is when the user only selects a couple of the combo
boxes. The other combo boxes that have not been selected are still blank.
This creates a problem because I want all values if the person did not select
an item in the combo box. Help!
 
Gary, you need to post the SQL of the query.

Probably you're not handling the empty (NULL) values gracefully, but
it's hard to tell without seeing the SQL.
 
If(IsNull([Forms]![frmRunSites]![FindId])=True,"like '*' or is
null",[Forms]![frmRunSites]![FindId])

I am using this in the selection criteria of the query.
--
Thanks!
Gary S


Danny J. Lesandrini said:
Gary, you need to post the SQL of the query.

Probably you're not handling the empty (NULL) values gracefully, but
it's hard to tell without seeing the SQL.
Click to expand...
 
I don't believe I've used the IIF function in a criteria, but it could be that
you spelled it wrong. It's not IF but IIF

You could reformulate it this way

LIKE Nz([Forms]![frmRunSites]![FindId],"'*'")

If FindID is NULL, it will evaluate into LIKE *. If not, it will be LIKE 1234
Where not wild card character is included with the LIKE operator, it is
effectively an EQUALS.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Gary S said:
If(IsNull([Forms]![frmRunSites]![FindId])=True,"like '*' or is
null",[Forms]![frmRunSites]![FindId])

I am using this in the selection criteria of the query.
Click to expand...
 
Well, you can't do it that way. IF the field you are searching against
always has a value (no nulls allowed) and the field is a text field then
you can use criteria like the following.

LIKE NZ([Forms]![frmRunSites]![FindId],"*")

Alternative is to use
= [Forms]![frmRunSites]![FindId] or [Forms]![frmRunSites]![FindId] is Null

The problem with the alternative above is that if you use the construct
many times the query will quickly grow too complex and will not run.

If the field is a date value and always has a value (no nulls) and you
know the maximum range of the dates you can use something like the
following.

Between NZ([Forms]![frmRunSites]![SomeDate],#1800-01-01#) and
NZ([Forms]![frmRunSites]![SomeDate],#3000-01-01#)

The best way to handle this type of situation is to use VBA to build the
criteria. Allen Browne has an example on his site - unfortunately I
can't find the URL right now.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
Danny,
That is better, but if the there is a null in the query field, it
won't pick up that record, any ideas.
--
Thanks!
Gary S


Danny J. Lesandrini said:
I don't believe I've used the IIF function in a criteria, but it could be that
you spelled it wrong. It's not IF but IIF

You could reformulate it this way

LIKE Nz([Forms]![frmRunSites]![FindId],"'*'")

If FindID is NULL, it will evaluate into LIKE *. If not, it will be LIKE 1234
Where not wild card character is included with the LIKE operator, it is
effectively an EQUALS.
--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Gary S said:
If(IsNull([Forms]![frmRunSites]![FindId])=True,"like '*' or is
null",[Forms]![frmRunSites]![FindId])

I am using this in the selection criteria of the query.
--
Thanks!
Gary S


Danny J. Lesandrini said:
Gary, you need to post the SQL of the query.

Probably you're not handling the empty (NULL) values gracefully, but
it's hard to tell without seeing the SQL.


--
Danny J. Lesandrini
(e-mail address removed)
www.amazecreations.com


Hi,

I have a query that is referencing a form that has multiple combo boxes. I
want this query to use each of the combo boxes as selection criteria. The
problem I am having is when the user only selects a couple of the combo
boxes. The other combo boxes that have not been selected are still blank.
This creates a problem because I want all values if the person did not select
an item in the combo box. Help!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top