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
Op 8-4-2010 6:33, Dennis schreef:
First of all, thank you for responding! I will answer you below your
questions.
[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
