Optional Parameters

[GD]

Can someone walk me through writing code (or any other solution) for a
parameter query that uses a form to select the parameter criteria. The
search is for possible text strings. What I need is for the text boxes on
the criteria form that are left blank to be ignored by the query, rather than
having the entire table returned as a result, if any are left blank. I can
email the database, so you can take a closer look at the details.

 

[Duane Hookom]

The typical solution is to set the criteria to something like:

Forms!frmYourCrits!txtCriteria Or Forms!frmYourCrits!txtCriteria Is Null

If you are using the query as the Record Source for a form or report, I
generally remove the dynamice criteria and apply the filtering in the "Where
Condition" of the DoCmd.OpenReport or DoCmd.OpenForm method.

 

[GD]

I appreciate your speedy response. However, I am new to Access, hence the
request for a walk through.

Here are the details: I have five text boxes in my parameter criteria form,
enabling the user to enter up to five text strings that the query will search
for in the table. I would like them to be able to enter anywhere between one
and five, leaving the possibility that any one of them (besides the 1st, I
suppose) will be left blank.

Also, the query will need to search through four separate fields of the
table for each one of these five text strings. Right now, the criteria row
for each of these four fields is as follows:

Like "*" & [Forms]![frmCMDMSearch]![Text21] & "*" Or Like "*" &
[Forms]![frmCMDMSearch]![Text23] & "*" Or Like "*" &
[Forms]![frmCMDMSearch]![Text25] & "*" Or Like "*" &
[Forms]![frmCMDMSearch]![Text27] & "*" Or Like "*" &
[Forms]![frmCMDMSearch]![Text29] & "*"

This works great, AS LONG AS ALL FIVE TEXT BOXES ON THE CRITERIA FORM ARE
NOT BLANK. As long as something is in all five (either intended criteria, or
a dummy symbol - XXX, `, @, etc.), it works. If any are left blank, it
returns the entire table as the query's result. Using your suggestion:

(Like "*" & [Forms]![frmCMDMSearch]![Text21] & "*" Or Like "*" &
[Forms]![frmCMDMSearch]![Text21] & "*" Is Null) Or (Like "*" &
[Forms]![frmCMDMSearch]![Text23] & "*" Or Like "*" &
[Forms]![frmCMDMSearch]![Text23] & "*" Is Null) Or (Like "*" &
[Forms]![frmCMDMSearch]![Text25] & "*" Or Like "*" &
[Forms]![frmCMDMSearch]![Text25] & "*" Is Null) Or (Like "*" &
[Forms]![frmCMDMSearch]![Text27] & "*" Or Like "*" &
[Forms]![frmCMDMSearch]![Text27] & "*" Is Null) Or (Like "*" &
[Forms]![frmCMDMSearch]![Text29] & "*" Or Like "*" &
[Forms]![frmCMDMSearch]![Text29] & "*" Is Null)

Returns the exact same result if one or more of the criteria text boxes are
left blank. The entire table is returned as a result. I need for those
empty boxes to be ignored. Can you still help? Please keep in mind that I
am a novice at this.

 

[Michel Walsh]

It is because you need to AND the sequences.


In the graphical editor, bring the actual field under which the criteria is
written a total of 6 times (in six columns), and, under one of these column,
type the criteria:

Like "*" & [Forms]![frmCMDMSearch]![Text21] & "*"


Then under another copy (or more frequently, under another field) , add the
criteria
Like "*" & [Forms]![frmCMDMSearch]![Text23] & "*"


and so on.


You don't need the "OR IS NULL" , here, since you use the operator LIKE.
You would need it if you were using strict equality.

 

[John Spencer]

You might try forcing an value that is known NOT to be in the field when the
control is blank. For instance the string $$@@() would almost never be in a
field.

Like "*" & NZ([Forms]![frmCMDMSearch]![Text21], "$$@@()") & "*"
Or Like "*" & NZ([Forms]![frmCMDMSearch]![Text23], "$$@@()") & "*"
Or Like "*" & NZ([Forms]![frmCMDMSearch]![Text25], "$$@@()") & "*"
Or Like "*" & NZ([Forms]![frmCMDMSearch]![Text27], "$$@@()") & "*"
Or Like "*" & NZ([Forms]![frmCMDMSearch]![Text29], "$$@@()") & "*"

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[GD]

Your solution solves one problem, but creates another. If I enter one text
string as criteria, it works perfectly. All records that have that text
string within one of the four fields specified are returned as the query's
result. In my test, it amounted to 13 records.

However, if I add a second criteria, no records are returned. My test above
should have returned no less than 13 records again, since the my original
criteria remains. In other words, I don't want all criteria provided to have
to be present in any given record in order for it to be returned as a result.
If any of the criteria provided is present in any one of the fields
selected, those records should be included in the query's results.

Right now the criteria row for my first column shows:

Like "*" & [Forms]![frmCMDMSearch]![Text21] & "*" And Like "*" &
[Forms]![frmCMDMSearch]![Text23] & "*" And Like "*" &
[Forms]![frmCMDMSearch]![Text25] & "*" And Like "*" &
[Forms]![frmCMDMSearch]![Text27] & "*" And Like "*" &
[Forms]![frmCMDMSearch]![Text29] & "*"

However, I should have mentioned before that this same expression appears
one row lower for my second field, and one row beneath that for the third,
etc. Is that correct?

Can you still help?

 

[GD]

FANTASTIC!! Works like a charm! One more question:

Is there any way to speed up a query like this? I'm working with a very
large table, so each query takes 15-20 seconds.
--
GD

You might try forcing an value that is known NOT to be in the field when the
control is blank. For instance the string $$@@() would almost never be in a
field.

Like "*" & NZ([Forms]![frmCMDMSearch]![Text21], "$$@@()") & "*"
Or Like "*" & NZ([Forms]![frmCMDMSearch]![Text23], "$$@@()") & "*"
Or Like "*" & NZ([Forms]![frmCMDMSearch]![Text25], "$$@@()") & "*"
Or Like "*" & NZ([Forms]![frmCMDMSearch]![Text27], "$$@@()") & "*"
Or Like "*" & NZ([Forms]![frmCMDMSearch]![Text29], "$$@@()") & "*"

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I appreciate your speedy response. However, I am new to Access, hence the
request for a walk through.

Here are the details: I have five text boxes in my parameter criteria form,
enabling the user to enter up to five text strings that the query will search
for in the table. I would like them to be able to enter anywhere between one
and five, leaving the possibility that any one of them (besides the 1st, I
suppose) will be left blank.

Also, the query will need to search through four separate fields of the
table for each one of these five text strings. Right now, the criteria row
for each of these four fields is as follows:

Like "*" & [Forms]![frmCMDMSearch]![Text21] & "*" Or Like "*" &
[Forms]![frmCMDMSearch]![Text23] & "*" Or Like "*" &
[Forms]![frmCMDMSearch]![Text25] & "*" Or Like "*" &
[Forms]![frmCMDMSearch]![Text27] & "*" Or Like "*" &
[Forms]![frmCMDMSearch]![Text29] & "*"

This works great, AS LONG AS ALL FIVE TEXT BOXES ON THE CRITERIA FORM ARE
NOT BLANK. As long as something is in all five (either intended criteria, or
a dummy symbol - XXX, `, @, etc.), it works. If any are left blank, it
returns the entire table as the query's result. Using your suggestion:

(Like "*" & [Forms]![frmCMDMSearch]![Text21] & "*" Or Like "*" &
[Forms]![frmCMDMSearch]![Text21] & "*" Is Null) Or (Like "*" &
[Forms]![frmCMDMSearch]![Text23] & "*" Or Like "*" &
[Forms]![frmCMDMSearch]![Text23] & "*" Is Null) Or (Like "*" &
[Forms]![frmCMDMSearch]![Text25] & "*" Or Like "*" &
[Forms]![frmCMDMSearch]![Text25] & "*" Is Null) Or (Like "*" &
[Forms]![frmCMDMSearch]![Text27] & "*" Or Like "*" &
[Forms]![frmCMDMSearch]![Text27] & "*" Is Null) Or (Like "*" &
[Forms]![frmCMDMSearch]![Text29] & "*" Or Like "*" &
[Forms]![frmCMDMSearch]![Text29] & "*" Is Null)

Returns the exact same result if one or more of the criteria text boxes are
left blank. The entire table is returned as a result. I need for those
empty boxes to be ignored. Can you still help? Please keep in mind that I
am a novice at this.

 

[Michel Walsh]

So, it is really one field, not five. Indeed, one single field cannot be
'equal' to two different string. So in the end, it sounds that the problem
needs a OR sequence of AND sub-sequences.

It is easier in SQL view. The criteria should then appear like:

... WHERE ( [Forms]![frmCMDMSearch]![Text21] IS NOT NULL AND
yourFirstColumn Like "*" & [Forms]![frmCMDMSearch]![Text21] & "*" )
OR ( [Forms]![frmCMDMSearch]![Text23] IS NOT NULL AND yourFirstColumn
Like "*" & [Forms]![frmCMDMSearch]![Text23] & "*" )
OR ( [Forms]![frmCMDMSearch]![Text25] IS NOT NULL AND yourFirstColumn
Like "*" & [Forms]![frmCMDMSearch]![Text25] & "*" )
OR ( [Forms]![frmCMDMSearch]![Text27] IS NOT NULL AND yourFirstColumn
Like "*" & [Forms]![frmCMDMSearch]![Text27] & "*" )
OR ( [Forms]![frmCMDMSearch]![Text29] IS NOT NULL AND yourFirstColumn
Like "*" & [Forms]![frmCMDMSearch]![Text29] & "*" )



Note that it is much easier if you can use equality, rather than LIKE, since
then what you seek is probably:

... WHERE yourFirstColumn IN( [Forms]![frmCMDMSearch]![Text21],
[Forms]![frmCMDMSearch]![Text23], [Forms]![frmCMDMSearch]![Text25],
[Forms]![frmCMDMSearch]![Text27], [Forms]![frmCMDMSearch]![Text29])




Vanderghast, Access MVP

Your solution solves one problem, but creates another. If I enter one
text
string as criteria, it works perfectly. All records that have that text
string within one of the four fields specified are returned as the query's
result. In my test, it amounted to 13 records.

However, if I add a second criteria, no records are returned. My test
above
should have returned no less than 13 records again, since the my original
criteria remains. In other words, I don't want all criteria provided to
have
to be present in any given record in order for it to be returned as a
result.
If any of the criteria provided is present in any one of the fields
selected, those records should be included in the query's results.

Right now the criteria row for my first column shows:

Like "*" & [Forms]![frmCMDMSearch]![Text21] & "*" And Like "*" &
[Forms]![frmCMDMSearch]![Text23] & "*" And Like "*" &
[Forms]![frmCMDMSearch]![Text25] & "*" And Like "*" &
[Forms]![frmCMDMSearch]![Text27] & "*" And Like "*" &
[Forms]![frmCMDMSearch]![Text29] & "*"

However, I should have mentioned before that this same expression appears
one row lower for my second field, and one row beneath that for the third,
etc. Is that correct?

Can you still help?
--
GD

It is because you need to AND the sequences.


In the graphical editor, bring the actual field under which the criteria
is
written a total of 6 times (in six columns), and, under one of these
column,
type the criteria:

Like "*" & [Forms]![frmCMDMSearch]![Text21] & "*"


Then under another copy (or more frequently, under another field) , add
the
criteria
Like "*" & [Forms]![frmCMDMSearch]![Text23] & "*"


and so on.


You don't need the "OR IS NULL" , here, since you use the operator LIKE.
You would need it if you were using strict equality.

 

[John Spencer]

The query cannot use indexes since you are looking for a match anywhere in the
field.

It could be faster if you were looking for the start of the field. Then the
query could use any indexes you would have on the field. So the following
could be faster - if you have an index on the field and you want to get
records that match the criteria at the start.

Like NZ([Forms]![frmCMDMSearch]![Text21], "$$@@()") & "*"
Or Like NZ([Forms]![frmCMDMSearch]![Text23], "$$@@()") & "*"
Or Like NZ([Forms]![frmCMDMSearch]![Text25], "$$@@()") & "*"
Or Like NZ([Forms]![frmCMDMSearch]![Text27], "$$@@()") & "*"
Or Like NZ([Forms]![frmCMDMSearch]![Text29], "$$@@()") & "*"


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County