query display all records if no criteria entered

[Ray S.]

I want to set up a form that will have a series of text boxes. The idea is
that the user can enter filtering criteria into the boxes. I want that if the
user enters no criteria a button on the form will run a query and return all
the records. If the user enters criteria in just one of the boxes (say it is
labeled, for example, "account number"), and pushes the button, I want the
query to use that criteria to filter on the column assigned to it. If the
user enters multiple criteria, I'd like the query to filter on the entered
criteria as elements in a filtering statement like "criteria1" AND
"criteria2" ... "criteriaN".
Can I get a little help putting this together?

 

[Allen Browne]

See:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

The article includes a sample database that illustrates how to build the
filter string from only the boxes where the user entered some criteria. It
demonstrates working with different kinds of data (text, numbers, dates),
exact matches, partial matches, and a date range.

It will take you a few moments to work though the code, but it's a technique
that is certainly worth learning.

 

[Tom van Stiphout]

On Thu, 23 Oct 2008 06:36:02 -0700, Ray S.

In the click event of that button you need to write the VBA code to
inspect the fields, concatenate the Filter expression, and set it as
the form property. Then set FilterOn=True to apply it.
If no textboxes filled, you set Filter="".

Since it is hard for people to remember exact account numbers, you may
want to offer a dropdown list instead.

-Tom.
Microsoft Access MVP

 

[Tom van Stiphout]

On Thu, 23 Oct 2008 06:36:02 -0700, Ray S.

I forgot to mention: all of this can also be done with the built-in
Filter-by-Form option. Why do it the hard way?

-Tom.
Microsoft Access MVP