Parametric queries

[Amin]

So I have three sequentialqueries: A, B, and C.

'A' queries a table, 'B' queries the results of 'A's queries, and 'C'
queries the results of 'B's queries.

'A' uses a WHERE condition that uses a parametric query
(i.e. WHERE thedate = [Parametric query])

My question is that when I run a report that uses the data from 'C', I have
to input the date 3 times. How can I stop this?

Also, I have a lot of queries that I use for my switchboard. Is there a way
I can group these into folders or something, so my queries screen is not so
packed?

Thanks for any thoughts!

 

[Ken Snell \(MVP\)]

I assume that the data from "C" are being used by the report for grouping on
the report. Each grouped value will cause the report to ask for the
parameter again, because of how reports get their data.

The way to avoid this problem is to have the "A" query read the parameter
value from a textbox or other control on an open form, instead of having you
manually enter the value for the query. Is the use of a form possible for
you to do in your database?

 

[KARL DEWEY]

I have to input the date 3 times. How can I stop this?
You can use a form to enter the date and in your queries refer to the form
text box like this --
[Forms]![YourFormName]![TextBoxName]

If you have the parameter in query A why do you need it in the other
queries? You can have it to be outputted from query A by using a calculated
field like this --
My_Date_Prompt: [Enter the date]
Just make sure to copy the parameter you presently have into the calculated
field so there are no differences in typing.

 

[Jerry Whittle]

You could roll-up the three queries into one. It might entail using subqueries.

You could put the criteria into a form field and run the query from a form.
The criteria would look something like [Forms]![Form Name]![Field Name] . The
form needs to stay open, but can be minimized or made invisible for the query
to work.

When you run query C, does it call for the parameter the same number of
times as when the report is run? If not, you could actually have something
like an index calling on a non-existant field or a misnamed field on the
report.

Also make sure that the parameter is defined as Date/Time in the query. In
query design view go to Query, Parameters. There should be an appropriate
entry there.

 

[fredg]

So I have three sequentialqueries: A, B, and C.

'A' queries a table, 'B' queries the results of 'A's queries, and 'C'
queries the results of 'B's queries.

'A' uses a WHERE condition that uses a parametric query
(i.e. WHERE thedate = [Parametric query])

My question is that when I run a report that uses the data from 'C', I have
to input the date 3 times. How can I stop this?

Also, I have a lot of queries that I use for my switchboard. Is there a way
I can group these into folders or something, so my queries screen is not so
packed?

Thanks for any thoughts!

Don't have enough information as to HOW you are opening the queries
nor exactly what your parameter is, but you should be able to adapt
this method.

Instead of using parameter queries that prompt for the date, use an
unbound form.

Create a form.
Add 2 controls.
Set the format property of each control to a valid Date format.
Name one control "StartDate".
Name the other control "EndDate"
Add a Command button. Set it's click event to:

Me.Visible = False

Name this Form "ParamForm"

In each query, change the criteria from [Enter Date] (or whatever you
now have) to

Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

Then when ever you need to run any or all of the queries, have your
switchboard first open this form:
DoCmd.OpenForm "ParamForm", , , , , acDialog

Then in the same procedure open whichever query you wish:
DoCmd.OpenQuery "QueryName"

When you run the query, the switchboard will first open the form.
Enter the dates. Click the Command Button. The queries will run
without you being prompted for the dates. Add code to the switchboard
to close the form when the final query is done.
DoCmd.Close acForm, "ParamForm"

 

[Amin]

Thanks for all the advice. I guess I was a little unclear with what I was
talking about. I have this giant table "X", and it takes three queries (A, B
& C) to get what I want to put in my report. The first query A, looks like
this:

SELECT WFLOW.*
FROM WFLOW
WHERE EVENTDATE=Forms!Dates![Fix Date];

"B" & "C" query A. A report works off of both "B" & "C". So I run the query
off of this form that has a text box called [Fix Date] and a command button
that runs the report. When I run it like this, I am no longer prompted three
times, but the report does not have any of the data. When I just double-click
on the report, I get the right results but now I am prompted three times with
this box:

Forms!Dates!Fix Date

This is probably still unclear and hard to determine the problem, but does
anyone have any thoughts?

Thanks again,
Amin

I assume that the data from "C" are being used by the report for grouping on
the report. Each grouped value will cause the report to ask for the
parameter again, because of how reports get their data.

The way to avoid this problem is to have the "A" query read the parameter
value from a textbox or other control on an open form, instead of having you
manually enter the value for the query. Is the use of a form possible for
you to do in your database?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

So I have three sequentialqueries: A, B, and C.

'A' queries a table, 'B' queries the results of 'A's queries, and 'C'
queries the results of 'B's queries.

'A' uses a WHERE condition that uses a parametric query
(i.e. WHERE thedate = [Parametric query])

My question is that when I run a report that uses the data from 'C', I
have
to input the date 3 times. How can I stop this?

Also, I have a lot of queries that I use for my switchboard. Is there a
way
I can group these into folders or something, so my queries screen is not
so
packed?

Thanks for any thoughts!

 

[Ken Snell \(MVP\)]

Is the FixDate textbox formatted as a date value (e.g., Short Date)? If not,
then it's possible that the query is not seeing the value in FixDate textbox
as a date value. So either format the FixDate textbox using its Format
property, or change your query to this to clearly specify that the textbox
is a date value:

PARAMETERS Forms!Dates![Fix Date] DateTime;
SELECT WFLOW.*
FROM WFLOW
WHERE EVENTDATE=Forms!Dates![Fix Date];


Or

SELECT WFLOW.*
FROM WFLOW
WHERE EVENTDATE=Format(Forms!Dates![Fix Date],
"\#mm\/dd\/yyyy\#");

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Thanks for all the advice. I guess I was a little unclear with what I was
talking about. I have this giant table "X", and it takes three queries (A,
B
& C) to get what I want to put in my report. The first query A, looks like
this:

SELECT WFLOW.*
FROM WFLOW
WHERE EVENTDATE=Forms!Dates![Fix Date];

"B" & "C" query A. A report works off of both "B" & "C". So I run the
query
off of this form that has a text box called [Fix Date] and a command
button
that runs the report. When I run it like this, I am no longer prompted
three
times, but the report does not have any of the data. When I just
double-click
on the report, I get the right results but now I am prompted three times
with
this box:

Forms!Dates!Fix Date

This is probably still unclear and hard to determine the problem, but does
anyone have any thoughts?

Thanks again,
Amin

I assume that the data from "C" are being used by the report for grouping
on
the report. Each grouped value will cause the report to ask for the
parameter again, because of how reports get their data.

The way to avoid this problem is to have the "A" query read the parameter
value from a textbox or other control on an open form, instead of having
you
manually enter the value for the query. Is the use of a form possible for
you to do in your database?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

So I have three sequentialqueries: A, B, and C.

'A' queries a table, 'B' queries the results of 'A's queries, and 'C'
queries the results of 'B's queries.

'A' uses a WHERE condition that uses a parametric query
(i.e. WHERE thedate = [Parametric query])

My question is that when I run a report that uses the data from 'C', I
have
to input the date 3 times. How can I stop this?

Also, I have a lot of queries that I use for my switchboard. Is there a
way
I can group these into folders or something, so my queries screen is
not
so
packed?

Thanks for any thoughts!

 

[Amin]

Thank you Ken!

Works perfectly!

Is the FixDate textbox formatted as a date value (e.g., Short Date)? If not,
then it's possible that the query is not seeing the value in FixDate textbox
as a date value. So either format the FixDate textbox using its Format
property, or change your query to this to clearly specify that the textbox
is a date value:

PARAMETERS Forms!Dates![Fix Date] DateTime;
SELECT WFLOW.*
FROM WFLOW
WHERE EVENTDATE=Forms!Dates![Fix Date];


Or

SELECT WFLOW.*
FROM WFLOW
WHERE EVENTDATE=Format(Forms!Dates![Fix Date],
"\#mm\/dd\/yyyy\#");

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Thanks for all the advice. I guess I was a little unclear with what I was
talking about. I have this giant table "X", and it takes three queries (A,
B
& C) to get what I want to put in my report. The first query A, looks like
this:

SELECT WFLOW.*
FROM WFLOW
WHERE EVENTDATE=Forms!Dates![Fix Date];

"B" & "C" query A. A report works off of both "B" & "C". So I run the
query
off of this form that has a text box called [Fix Date] and a command
button
that runs the report. When I run it like this, I am no longer prompted
three
times, but the report does not have any of the data. When I just
double-click
on the report, I get the right results but now I am prompted three times
with
this box:

Forms!Dates!Fix Date

This is probably still unclear and hard to determine the problem, but does
anyone have any thoughts?

Thanks again,
Amin

I assume that the data from "C" are being used by the report for grouping
on
the report. Each grouped value will cause the report to ask for the
parameter again, because of how reports get their data.

The way to avoid this problem is to have the "A" query read the parameter
value from a textbox or other control on an open form, instead of having
you
manually enter the value for the query. Is the use of a form possible for
you to do in your database?
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/




So I have three sequentialqueries: A, B, and C.

'A' queries a table, 'B' queries the results of 'A's queries, and 'C'
queries the results of 'B's queries.

'A' uses a WHERE condition that uses a parametric query
(i.e. WHERE thedate = [Parametric query])

My question is that when I run a report that uses the data from 'C', I
have
to input the date 3 times. How can I stop this?

Also, I have a lot of queries that I use for my switchboard. Is there a
way
I can group these into folders or something, so my queries screen is
not
so
packed?

Thanks for any thoughts!

 

[knechod]

On Fri, 7 Nov 2008 07:52:01 -0800, Amin wrote:
Instead of using parameter queries that prompt for the date, use an
unbound form.

Create a form.
Add 2 controls.
Set the format property of each control to a valid Date format.
Name one control "StartDate".
Name the other control "EndDate"
Add a Command button. Set it's click event to:

Me.Visible = False

Name this Form "ParamForm"

In each query, change the criteria from [Enter Date] (or whatever you
now have) to

Between forms!ParamForm!StartDate and forms!ParamForm!EndDate

One of the drawbacks of this approach is that the query (and the
report built on it) is dependent upon that form being active. I was
hoping to use query parameters, so that it would prompt if not
submitted through a form, and skip the prompts if supplied by the
form.

Am I out of luck?

Thanks,

Kevin

 

[John W. Vinson]

One of the drawbacks of this approach is that the query (and the
report built on it) is dependent upon that form being active. I was
hoping to use query parameters, so that it would prompt if not
submitted through a form, and skip the prompts if supplied by the
form.

Am I out of luck?

If the form isn't open you WILL get prompted (for
[Forms]![ParamForm]![Startdate], which may be confusing to your users).

One solution is to put a command button on ParamForm to launch the report, and
not allow the user (or not make it easy for the user) to open the report
directly; another approach is to open ParamForm from the Report's Open event.