A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

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



 
 
Thread Tools Display Modes
  #1  
Old March 18th, 2010, 08:32 PM posted to microsoft.public.access.gettingstarted
Lee Ann[_2_]
external usenet poster
 
Posts: 38
Default Sorting records based on a couple or many criteria items from a qu

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.
  #2  
Old March 18th, 2010, 11:31 PM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Sorting records based on a couple or many criteria items from a qu

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/For...arted/201003/1

  #3  
Old March 19th, 2010, 02:59 AM posted to microsoft.public.access.gettingstarted
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Sorting records based on a couple or many criteria items from a qu

On Thu, 18 Mar 2010 22:31:22 GMT, "KenSheridan via AccessMonster.com"
u51882@uwe wrote:

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.


Just one warning: NEVER open this query in Design View - only in SQL view.
Access will make a total hash of it.
--

John W. Vinson [MVP]
  #4  
Old March 22nd, 2010, 02:33 PM posted to microsoft.public.access.gettingstarted
Lee Ann[_2_]
external usenet poster
 
Posts: 38
Default Sorting records based on a couple or many criteria items from

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/For...arted/201003/1

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:02 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.