Multi-field search form

Hi there,

I am rather new to this place, so I would like to thank you all up front
for any and all assistance you may provide.

It has been a while since I worked with MS Access last. What I am trying
to do is create a multi-field search form, where any of the fields can
be filled which will display a result set. Preferably if no selection in
the fields is made, the entire resultset should be shown - this is not a
requirement, though.

I seem to be a bit at a loss on how to procede with this task or to find
any resources to help me get along.

Again, thanks for any and all assistance. If you require more
information, please let me know!

Kind regards,
Kevin

Kevin,

I'm not quite sure what you mean by "a multi-field search form, where any of
the fields can be filled which will display a result set."

Are you saying that you want to create a form that has multiple text boxes
where each text box refers to a different field? When the user enters
something in the different text controls, you want to search on them.

If the user enters multiple selections do you want to do an "AND" or "OR'
search? That is, if the user enters something in the cust name and address
search boxes, which of the following searches do you want to do?

1. Select CustomerTbl Where CustName = "data" AND Address = "data"

or

2. Select CustomerTbl Where CustName = "data" OR Address = "data"


These queries produce two VERY different result.

Dennis

Op 8-4-2010 6:33, Dennis schreef:

First of all, thank you for responding! I will answer you below your
questions.

Kevin,

I'm not quite sure what you mean by "a multi-field search form, where any of
the fields can be filled which will display a result set."

Are you saying that you want to create a form that has multiple text boxes
where each text box refers to a different field? When the user enters
something in the different text controls, you want to search on them.

That is correct. For instance a the search form offers the options to
search on "Name" and "Zipcode". When only the zipcode would be entered
on might find a larger result set then with "name". However, if one
searches on "name" they will get another result set. In either case the
one entering the search queries will have to go through the result set
to find the correct record. When both are entered the result set is a
lot smaller and thus the correct record could easily be located.


If the user enters multiple selections do you want to do an "AND" or "OR'
search? That is, if the user enters something in the cust name and address
search boxes, which of the following searches do you want to do?

1. Select CustomerTbl Where CustName = "data" AND Address = "data"

or

2. Select CustomerTbl Where CustName = "data" OR Address = "data"

I realize I have been unclear. What I am looking for is an "AND"
solution, where not all search fields will have to be filled. But the
more information is entered on the search form the smaller the result
set should become.


These queries produce two VERY different result.

Dennis



I hope this clarifies what I am looking for a bit. Again, thank you for
responding.

Kevin

Kevin:

Create an unbound dialogue form with text boxes for each of the fields you
want to use as the parameters, txtName, txtZipCode etc. Then create a query
based on your table or tables, which references each of the controls on the
search form as a parameter, and in each case, as well as testing for a match,
test for OR the parameter IS NULL, so with the two fields you mention, a
query would be like this:

SELECT *
FROM [MyTable]
WHERE ([MyTable].[Name] = Forms![frmSearch]![txtName]
OR Forms![frmSearch]![txtName] IS NULL)
AND ([MyTable].[ZipCode] = Forms![frmSearch]![txtZipCode]
OR Forms![frmSearch]![txtZipCode] IS NULL);

Make sure you save this query in SQL view, not in design view. If you switch
to design view and save it Access will move things around; at best the
underlying logic will be unclear; at worst it will no longer work.

BTW if you have used 'Name' as a field name I'd advise against it as it’s the
name of a built in property in Access. Always use explicit terms like
CustomerName etc. If you do use 'reserved' words be sure to qualify the
field name with the table name in a query.

Add a button to the form to open the query, or better still a form or report
based on the query.

You'll see that in the query's WHERE clause each OR operation is enclosed in
parentheses to force them each to evaluate independently of the AND
operations, so you can add as many more parenthesised OR operations as you
wish to correspond to additional controls on the search form, tacking them
together with ANDs.

If you want to use a single bound form rather than a separate unbound
dialogue form then you'd base the form on the query and include unbound
controls in which to enter the parameter values and bound controls to show
the results. In this case you'd requery the form in the AfterUpdate event
procedure of each of the unbound parameter controls with:

Me.Requery

As a value is entered in each unbound control the form will be requeried to
show the matching records.

Ken Sheridan
Stafford, England

Kevin wrote:
Op 8-4-2010 6:33, Dennis schreef:

First of all, thank you for responding! I will answer you below your
questions.

Kevin,

[quoted text clipped - 4 lines]
where each text box refers to a different field? When the user enters
something in the different text controls, you want to search on them.

That is correct. For instance a the search form offers the options to
search on "Name" and "Zipcode". When only the zipcode would be entered
on might find a larger result set then with "name". However, if one
searches on "name" they will get another result set. In either case the
one entering the search queries will have to go through the result set
to find the correct record. When both are entered the result set is a
lot smaller and thus the correct record could easily be located.

If the user enters multiple selections do you want to do an "AND" or "OR'
search? That is, if the user enters something in the cust name and address
[quoted text clipped - 5 lines]

2. Select CustomerTbl Where CustName = "data" OR Address = "data"

I realize I have been unclear. What I am looking for is an "AND"
solution, where not all search fields will have to be filled. But the
more information is entered on the search form the smaller the result
set should become.

These queries produce two VERY different result.

Dennis

I hope this clarifies what I am looking for a bit. Again, thank you for
responding.

Kevin

--
Message posted via http://www.accessmonster.com

On Wed, 07 Apr 2010 22:54:12 +0200, "news.microsoft.com"
wrote:

Hi there,

I am rather new to this place, so I would like to thank you all up front
for any and all assistance you may provide.

It has been a while since I worked with MS Access last. What I am trying
to do is create a multi-field search form, where any of the fields can
be filled which will display a result set. Preferably if no selection in
the fields is made, the entire resultset should be shown - this is not a
requirement, though.

I seem to be a bit at a loss on how to procede with this task or to find
any resources to help me get along.

Again, thanks for any and all assistance. If you require more
information, please let me know!

Kind regards,
Kevin

You might want to look at Allen Browne's generic search form example:

http://allenbrowne.com/ser-62.html

--

John W. Vinson [MVP]