Sorting records based on a couple or many criteria items from a qu

  • Thread starter Thread starter Lee Ann
  • Start date Start date
L

Lee Ann

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.
 
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.
Click to expand...

Just one warning: NEVER open this query in Design View - only in SQL view.
Access will make a total hash of it.
 
This was exactly what I needed - thank you!

KenSheridan via AccessMonster.com said:
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 said:
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.
Click to expand...

--
Message posted via AccessMonster.com


.
Click to expand...
 
Back
Top