Prev: Creating a Query based of two diffent data tables
Next: How do you create a formula subtracting 1 field into a 2nd field
From: Lee Ann on 22 Mar 2010 09:33
This was exactly what I needed - thank you!

"KenSheridan via AccessMonster.com" wrote:

> That shouldn't be a problem. The trick is to test for a match in each combo
> box OR the combo box being NULL. This in effect makes selecting a value in
> each combo box optional. Eleven combo boxes should not cause any
> difficulties.
>
> Taking a simple example of three combo boxes for Field1, Field2 and Field3 in
> a table MyTable with three corresponding combo boxes cbo1, cb2 and cbo3 on a
> form MyForm (you'd be using meaningful names of course), the query would be:
>
> SELECT *
> FROM [MyTable]
> WHERE ([Field1 = Forms![MyForm]![cbo1]
> OR Forms![MyForm]![cbo1] IS NULL)
> AND ([Field2 = Forms![MyForm]![cbo2]
> OR Forms![MyForm]![cbo2] IS NULL)
> AND ([Field3 = Forms![MyForm]![cbo3]
> OR Forms![MyForm]![cbo3] IS NULL);
>
> Each OR operation must be enclosed in parentheses to force it to evaluate
> independently of the AND operations. You can therefore add as many more
> parenthesised OR operations as you wish, tacking them together with AND
> operations.
>
> Start with your existing query in design view and switch to SQL view. Then
> add the WHERE clause. I'd strongly recommend that you then save it in SQL
> view as if you switch to design view and save it you'll find that if you
> reopen it in design view Access will have moved things around a lot and
> you'll never get your teeth around the logic to make any amendments to the
> WHERE clause.
>
> Base a form and/or report on the query and in your dialogue form add a button
> or buttons to open the form and/or report, which will be restricted on the
> basis of your selections in the combo boxes.
>
> Ken Sheridan
> Stafford, England
>
> Lee Ann wrote:
> >I currently have a query set up consisting of several fields from several
> >tables. I'd like to solicit advise on the best way to set up search
> >criteria. I've looked into building a dialog box containing combo boxes
> >where the source of the combo boxes are individual queries. This is exactly
> >the type of mechanism I'm looking for, but according to the documentation
> >I've read I can't have more than two or three of the combo boxes on the
> >dialog box as it creates a complex query. I have about eleven different
> >fields I'd like to have the option to sort on - maybe sorting the records on
> >many of them or just a few. Any advice on the best way of going about this
> >is appreciated.
>
> --
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201003/1
>
> .
>
 | 
Pages: 1
Prev: Creating a Query based of two diffent data tables
Next: How do you create a formula subtracting 1 field into a 2nd field