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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|