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
|
|||
|
|||
One Query, different criteria
Hi
I'd be grateful if anyone could point me in the direction of some instructions on how to do this. I have not been able to find the answer on Access help, nor on this forum. In short, I would like run a query from a form using different criteria each time. At present I have copied the query 4 or 5 times and each time used a seperate criteria but this makes the database clunky. In more detail, from a Form I would like the user to run a query by pushing one of several buttons, each of which runs the same query using a different criterion. There are perhaps 10 ways to run the query, each with one or sometimes two criteria. The criteria correspond to different fields in the query, so the user can search for Incomplete Files, Files waiting to be sent, by Category, or Incomplete Files by Category etc. I want the choices laid out for the user to pick, therefore I expect to use several buttons, one for each version of the query. At present I have each button running a different saved query but each is an almost identical copy of the first so it seems pretty heavy. Hope this makes sense! How do I go about this or where do I look? Thanks for your help Cheers CK1 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200901/1 |
#2
|
|||
|
|||
One Query, different criteria
Perhaps this sample database will point you in the right direction. It shows
how to build WHERE clauses that can be used to filter forms/reports based on selections/entries made by the user on the first form. Building SQL string based on values entered into controls http://www.accessmvp.com/KDSnell/Sam...htm#FilterForm -- Ken Snell MS ACCESS MVP http://www.accessmvp.com/KDSnell/ "CK1 via AccessMonster.com" u48386@uwe wrote in message news:9063843162ede@uwe... Hi I'd be grateful if anyone could point me in the direction of some instructions on how to do this. I have not been able to find the answer on Access help, nor on this forum. In short, I would like run a query from a form using different criteria each time. At present I have copied the query 4 or 5 times and each time used a seperate criteria but this makes the database clunky. In more detail, from a Form I would like the user to run a query by pushing one of several buttons, each of which runs the same query using a different criterion. There are perhaps 10 ways to run the query, each with one or sometimes two criteria. The criteria correspond to different fields in the query, so the user can search for Incomplete Files, Files waiting to be sent, by Category, or Incomplete Files by Category etc. I want the choices laid out for the user to pick, therefore I expect to use several buttons, one for each version of the query. At present I have each button running a different saved query but each is an almost identical copy of the first so it seems pretty heavy. Hope this makes sense! How do I go about this or where do I look? Thanks for your help Cheers CK1 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200901/1 |
#3
|
|||
|
|||
One Query, different criteria
Thanks Ken. I've had a look at this and I must admit that the SQL language is
beyond me, I'm afraid. I had expected simply to be entering criteria on the design view of the query. Of course I can still use multiple copies of the same queries but if there is a way to use one query but several criteria I'm sure it would be neater. Can you suggest another way around this? I think I need to go back to basics! Cheers CK Ken Snell (MVP) wrote: Perhaps this sample database will point you in the right direction. It shows how to build WHERE clauses that can be used to filter forms/reports based on selections/entries made by the user on the first form. Building SQL string based on values entered into controls http://www.accessmvp.com/KDSnell/Sam...htm#FilterForm Hi [quoted text clipped - 28 lines] Cheers CK1 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200901/1 |
#4
|
|||
|
|||
One Query, different criteria
To what end use will the queries be put? For display on a form? For display
in a report? For exporting to a file? -- Ken Snell MS ACCESS MVP http://www.accessmvp.com/KDSnell/ "CK1 via AccessMonster.com" u48386@uwe wrote in message news:906fce305b20d@uwe... Thanks Ken. I've had a look at this and I must admit that the SQL language is beyond me, I'm afraid. I had expected simply to be entering criteria on the design view of the query. Of course I can still use multiple copies of the same queries but if there is a way to use one query but several criteria I'm sure it would be neater. Can you suggest another way around this? I think I need to go back to basics! Cheers CK Ken Snell (MVP) wrote: Perhaps this sample database will point you in the right direction. It shows how to build WHERE clauses that can be used to filter forms/reports based on selections/entries made by the user on the first form. Building SQL string based on values entered into controls http://www.accessmvp.com/KDSnell/Sam...htm#FilterForm Hi [quoted text clipped - 28 lines] Cheers CK1 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200901/1 |
#5
|
|||
|
|||
One Query, different criteria
Ken, the queries will be run to show the user where the gaps are in their
files or, conversely, which files are complete. So far I have presented it as a subform (in datasheet view) in a form. I anticipate that a report could be useful too, but a form initially. Thanks again best wishes CK Ken Snell (MVP) wrote: To what end use will the queries be put? For display on a form? For display in a report? For exporting to a file? Thanks Ken. I've had a look at this and I must admit that the SQL language is [quoted text clipped - 25 lines] Cheers CK1 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200901/1 |
#6
|
|||
|
|||
One Query, different criteria
You could use what is known as a querydef which is one query with
different criteria each time. For FREE Access ebook and videos click here http://access-databases.com/ebook On 18 Jan, 22:42, "CK1 via AccessMonster.com" u48386@uwe wrote: Hi I'd be grateful if anyone could point me in the direction of some instructions on how to do this. I have not been able to find the answer on Access help, nor on this forum. In short, I would like run a query from a form using different criteria each time. At present I have copied the query 4 or 5 times and each time used a seperate criteria but this makes the database clunky. In more detail, from a Form I would like the user to run a query by pushing one of several buttons, each of which runs the same query using a different criterion. There are perhaps 10 ways to run the query, each with one or sometimes two criteria. The criteria correspond to different fields in the query, so the user can search for Incomplete Files, Files waiting to be sent, by Category, or Incomplete Files by Category etc. I want the choices laid out for the user to pick, therefore I expect to use several buttons, one for each version of the query. At present I have each button running a different saved query but each is an almost identical copy of the first so it seems pretty heavy. Hope this makes sense! How do I go about this or where do I look? Thanks for your help Cheers CK1 -- Message posted via AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/20... |
#7
|
|||
|
|||
One Query, different criteria
If you open a form that shows the filtered data, you can base that form on
the query without the criterion string, and put the built WHERE clause info into the fourth argument of the DoCmd.OpenForm method: DoCmd.OpenForm "NameOfForm", , , "FieldName='SomeValue'" Doing this in a subform is a bit more tricky, because you cannot use the DoCmd.OpenForm action. Instead, you'd need to change the subform's RecordSource to be the SQL statement of the filtered query: SELECT * FROM QueryName WHERE FieldName = 'SomeValue' (Alternatively, you can apply a filter to a subform, but changing the RecordSource is easier, I think.) A report can be done similarly to the form, except that you'd use the DoCmd.OpenReport action. Is the subform the way you want to go? Is it in the same form where your user selects the desired values on the form? Can you give us details about form names, subform SourceObject name, subform control name, etc.? -- Ken Snell MS ACCESS MVP http://www.accessmvp.com/KDSnell/ "CK1 via AccessMonster.com" u48386@uwe wrote in message news:9076fdcb9ab0a@uwe... Ken, the queries will be run to show the user where the gaps are in their files or, conversely, which files are complete. So far I have presented it as a subform (in datasheet view) in a form. I anticipate that a report could be useful too, but a form initially. Thanks again best wishes CK Ken Snell (MVP) wrote: To what end use will the queries be put? For display on a form? For display in a report? For exporting to a file? Thanks Ken. I've had a look at this and I must admit that the SQL language is [quoted text clipped - 25 lines] Cheers CK1 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200901/1 |
Thread Tools | |
Display Modes | |
|
|