Access 2003 Query by form

[Guest]

Hi! I hope I have this in the right discussion group. Here is my problem: I
have a form which is to be used to enter query criteria for up to 14 fields.
I can get it to work, for the most part, by putting
[Forms]![FormName]![TextBoxName]
in the criteria portion of the query, for each field. However, if I leave a
text box blank, the query returns no records at all. I would rather have it
such that if a text box is left blank when the query is run that all entries
in that field will be accepted by the query. For example, if I was to leave
the Year entry blank, then I would get results from all years, limited by the
criteria entered for other fields.

Also, to make things just a little more complicated, I need most of the
fields to require exact entries to get results. By that I mean if I enter a 2
into text box that corresponds to a certain field, then I only want records
returned that have exaclty 2 in that field, not 12 or 2032 or any other
number that has two as one of its digits.

I don't have a lot of experience with Access, so complicated solutions may
be a little beyond me. I can post more specific details if need be. Thanks
for any help you can provide.

 

[Allen Browne]

It may take a bit of effort to get the right combination of flexibility and
accuracy for your query form.

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

The article explains how to build the Filter for the form from only those
boxes where the user enters something. It also explains some of the pitfalls
with trying to do it all in the query, and illustrates how it can be done.
Includes a sample database to download so you can pull it apart and see how
it works.

 

[Guest]

Thanks you very much! This was perfect, and I can even use this code to
improve another database.

It may take a bit of effort to get the right combination of flexibility and
accuracy for your query form.

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

The article explains how to build the Filter for the form from only those
boxes where the user enters something. It also explains some of the pitfalls
with trying to do it all in the query, and illustrates how it can be done.
Includes a sample database to download so you can pull it apart and see how
it works.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Hi! I hope I have this in the right discussion group. Here is my problem:
I
have a form which is to be used to enter query criteria for up to 14
fields.
I can get it to work, for the most part, by putting
[Forms]![FormName]![TextBoxName]
in the criteria portion of the query, for each field. However, if I leave
a
text box blank, the query returns no records at all. I would rather have
it
such that if a text box is left blank when the query is run that all
entries
in that field will be accepted by the query. For example, if I was to
leave
the Year entry blank, then I would get results from all years, limited by
the
criteria entered for other fields.

Also, to make things just a little more complicated, I need most of the
fields to require exact entries to get results. By that I mean if I enter
a 2
into text box that corresponds to a certain field, then I only want
records
returned that have exaclty 2 in that field, not 12 or 2032 or any other
number that has two as one of its digits.

I don't have a lot of experience with Access, so complicated solutions may
be a little beyond me. I can post more specific details if need be. Thanks
for any help you can provide.