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  

One Query, different criteria



 
 
Thread Tools Display Modes
  #1  
Old January 18th, 2009, 11:42 PM posted to microsoft.public.access.gettingstarted
CK1 via AccessMonster.com
external usenet poster
 
Posts: 5
Default 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  
Old January 19th, 2009, 12:05 AM posted to microsoft.public.access.gettingstarted
Ken Snell \(MVP\)
external usenet poster
 
Posts: 2,506
Default 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  
Old January 19th, 2009, 11:10 PM posted to microsoft.public.access.gettingstarted
CK1 via AccessMonster.com
external usenet poster
 
Posts: 5
Default 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  
Old January 20th, 2009, 12:41 AM posted to microsoft.public.access.gettingstarted
Ken Snell \(MVP\)
external usenet poster
 
Posts: 2,506
Default 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  
Old January 20th, 2009, 12:53 PM posted to microsoft.public.access.gettingstarted
CK1 via AccessMonster.com
external usenet poster
 
Posts: 5
Default 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  
Old January 20th, 2009, 10:01 PM posted to microsoft.public.access.gettingstarted
troy23
external usenet poster
 
Posts: 55
Default 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  
Old January 21st, 2009, 04:43 AM posted to microsoft.public.access.gettingstarted
Ken Snell \(MVP\)
external usenet poster
 
Posts: 2,506
Default 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

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 07:53 PM.


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