One Query, different criteria

[CK1 via AccessMonster.com]

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

 

[Ken Snell \(MVP\)]

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/SampleDBs.htm#FilterForm

 

[CK1 via AccessMonster.com]

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

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/SampleDBs.htm#FilterForm

Hi

[quoted text clipped - 28 lines]

Cheers
CK1

 

[Ken Snell \(MVP\)]

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/

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

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/SampleDBs.htm#FilterForm

Hi

[quoted text clipped - 28 lines]

Cheers
CK1

 

[CK1 via AccessMonster.com]

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

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]

 

[troy23]

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

 

[Ken Snell \(MVP\)]

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/

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

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