help me choose when a parameter is applied

[Guest]

I've built a search form with about 15 controls, and I want to build a query
that retrieves criteria from the form. However, I don't want users to be
required to select/enter a value for every control, just the ones they want
to limit. When they leave controls blank, I don't want to lose all the
records in the resultant query, NOR do I want to add an "Is Null" statement
in the OR section of the field because then the query will return all records.

What I need is something that will retrieve criteria values from the form if
a user selects/enters them, but otherwise treats the field as if no criteria
had been applied.

Is that possible?

 

[Guest]

Hi alek,

Sure it's possible. One solution is in the "Issues" database template you
can find here:
http://office.microsoft.com/en-us/templates/CT101426031033.aspx?av=ZAC000

Basically, it's uses a string variable in the 'OpenForm' method of the DoCmd
object. The form that opens is located in the form footer of the search form.

Just download the template, open it, open the search form in design mode,
and look at the code behind the 'click' event of the search button at the
bottom of that form. It will show you exactly how you can do what you want
to do....or at least ONE way to do it.

Hope that helps.
CW

 

[Larry Daugherty]

One way:

Assume that you want to find values that begin with the string you
enter and that if you enter nothing you want to accept all values in
this particular column.

In the query's criteria line: Like forms!MyCriteriaForm!ThisControl &
"*"

the query will return all values in that column that begin with the
string in ThisControl on MyCriteriaForm. Each control can work the
same. Limit to strings that begin with what's in the control or leave
the control blank to accept all values in that field.

You can easily see that you could use the same kind of concept to
return all rows that have what's in MyControl as part of the field.
And it's easy to see how you can apply the same thinking to just
return values that end with...

HTH

 

[John Spencer]

Be careful using this technique. If the field is blank this will screen
out records where the field is blank (null).

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Larry Daugherty]

Of course it does. those "*" have the meaning of "None or more".
Your concern addresses exactly the case in which you would create a
query with *no* entry in the criteria field.

HTH