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
0
news
4/7/2010 8:54:12 PM
access.forms 6864 articles. 2 followers. Follow

4 Replies
2517 Views

Similar Articles

[PageSpeed] 26

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


0
Utf
4/8/2010 4:33:01 AM
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
0
Kevin
4/8/2010 2:17:27 PM
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

0
KenSheridan
4/8/2010 3:46:18 PM
On Wed, 07 Apr 2010 22:54:12 +0200, "news.microsoft.com"
<clearance.level1@gmail.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]
0
John
4/8/2010 4:08:59 PM
Reply:

Similar Artilces: