Report Query Parameters

[Guest]

I have a parameter for a report, which, if it is included as a field in the
report - causes the summary not to SUM correctly.

I put the parameter in a textbox on a form
so that I could send criteria for that field without actually selecting it.

it would be the same as using "HAVING" in SQL when a field isn't selected.


The following line works perfectly if I type it directly into the query:
([Group #] Like '*') with the 'show' checkbox unchecked.

However, if I have the query refer to a textbox that has the exact same
text...
It comes up empty. What am I doing incorrectly?

 

[Guest]

how are you refering to the text box?
should look something like this:
WHERE (((parameter)>=[Forms]![yourform]![yourtextbox]

 

[Guest]

Thank you...
I have code that produces the correct comparison operator with the string of
the textbox:

=, like '*', =, <>

so my SQL statement in the query looks like this:

HAVING (((GroupDecision.[Group
#])=[Forms]![frmSelectReports]![frmSub]![lstBoxChoice]));

so lets say the string in the textbox were ([Group #] like '*')
... it should work right? and give me every group.

that same textbox could also give me ([Group #] IN('2223', '2224'))

All these strings work if I paste them directly into the query in place of
the parameter. Why don't they work if they're in a textbox as a parameter?

how are you refering to the text box?
should look something like this:
WHERE (((parameter)>=[Forms]![yourform]![yourtextbox]

-----Original Message-----
I have a parameter for a report, which, if it is included as a field in the
report - causes the summary not to SUM correctly.

I put the parameter in a textbox on a form
so that I could send criteria for that field without actually selecting it.

it would be the same as using "HAVING" in SQL when a field isn't selected.


The following line works perfectly if I type it directly into the query:
([Group #] Like '*') with the 'show' checkbox unchecked.

However, if I have the query refer to a textbox that has the exact same
text...
It comes up empty. What am I doing incorrectly?



.

 

[Marshall Barton]

I have code that produces the correct comparison operator with the string of
the textbox:

=, like '*', =, <>

so my SQL statement in the query looks like this:

HAVING (((GroupDecision.[Group
#])=[Forms]![frmSelectReports]![frmSub]![lstBoxChoice]));

so lets say the string in the textbox were ([Group #] like '*')
... it should work right? and give me every group.

that same textbox could also give me ([Group #] IN('2223', '2224'))

All these strings work if I paste them directly into the query in place of
the parameter. Why don't they work if they're in a textbox as a parameter?

That can not possibly work. A parameter must represent a
value in an expression, not the entire expression or even
part of an expression that includes an operator.

Also, you didn't post the rest of you query's SQL, but I'll
bet you should be using WHERE instead of HAVING. Where is
used to select the data for the query to process, Having is
used to filter out entire groups after the GROUP BY clause
has been completed.

Back to your problem. To filter on expressions, as you cite
above, you have to construct the entire SQL statement in
code. While you're doing that, it's easy to use the
parameter's value instead of asking the query to find it for
you. I'd be happy to take a shot at the code if you'll post
your query's entire SQL statement along with an explanation
of where/how it's being used.

 

[Guest]

Thanks a bunch.

Just as background, we want to be able to show summary data of complaints
that were overturned, upheld, and unresolved for certain groups.
The problem is, for each group that shows, the category is repeated.
The users don't care about the underlying groups in this particular report--
they only care about the each category and how many unresolved, upheld and
overturned there are.

In order to accomplish this, Group # or Group Name must be filtered but not
selected.

I am presently using a listbox to facilitate the user being able to select a
particular group they want to filter for. I've written code that
effectively creates a strFilter variable (and it would work if I passed it to
the docmd.Open report command) but now since they don't want the group # or
name to show, I can't have a query that filters but doesn't select unless I
fill it with something --hence the form textbox field.

The report that I open is called rptYTDDOIDOL_TPA
Yes, it would be great if I could change the underlying query of the report
before it opens on varying conditions.

Here is the entire query:

SELECT YTDDOIDOL01DGroupDecision.[Type of category],
Sum(YTDDOIDOL01DGroupDecision.Overturned) AS SumOfOverturned,
Sum(YTDDOIDOL01DGroupDecision.Upheld) AS SumOfUpheld,
Sum(YTDDOIDOL01DGroupDecision.Unresolved) AS SumOfUnresolved
FROM YTDDOIDOL01DGroupDecision
GROUP BY YTDDOIDOL01DGroupDecision.[Type of category],
YTDDOIDOL01DGroupDecision.[Group #]
HAVING (((YTDDOIDOL01DGroupDecision.[Group
#])=[Forms]![frmSelectReports]![frmSub]![lstBoxChoice]));

I have code that produces the correct comparison operator with the string of
the textbox:

=, like '*', =, <>

so my SQL statement in the query looks like this:

HAVING (((GroupDecision.[Group
#])=[Forms]![frmSelectReports]![frmSub]![lstBoxChoice]));

so lets say the string in the textbox were ([Group #] like '*')
... it should work right? and give me every group.

that same textbox could also give me ([Group #] IN('2223', '2224'))

All these strings work if I paste them directly into the query in place of
the parameter. Why don't they work if they're in a textbox as a parameter?

That can not possibly work. A parameter must represent a
value in an expression, not the entire expression or even
part of an expression that includes an operator.

Also, you didn't post the rest of you query's SQL, but I'll
bet you should be using WHERE instead of HAVING. Where is
used to select the data for the query to process, Having is
used to filter out entire groups after the GROUP BY clause
has been completed.

Back to your problem. To filter on expressions, as you cite
above, you have to construct the entire SQL statement in
code. While you're doing that, it's easy to use the
parameter's value instead of asking the query to find it for
you. I'd be happy to take a shot at the code if you'll post
your query's entire SQL statement along with an explanation
of where/how it's being used.

 

[Guest]

Thanks a bunch.

Here is some background

The report involves a summary of complaints that were
overturned, upheld or unresolved summarized by category type
At this point, the managers don't care about the group names or numbers
But they want to be able to filter by group #

In order to avoid having category types repeat in the final report,
I cannot show group# or Group Name so my parameter has to include criteria
for
Group # without selecting it

I have code that obtains whatever the user selects from a listbox
and they only want that listbox to be single select


The textbox lstBoxChoice is the result of a routine I wrote that passes a
strFilter
for the report. However, it does not work if [Group #] is not included in
the selection

Yes, a way to change the underlying query of the report before it opens
would help tremendously.

Here is the entire query:


SELECT YTDDOIDOL01DGroupDecision.[Type of category],
Sum(YTDDOIDOL01DGroupDecision.Overturned) AS

SumOfOverturned, Sum(YTDDOIDOL01DGroupDecision.Upheld) AS SumOfUpheld,

Sum(YTDDOIDOL01DGroupDecision.Unresolved) AS SumOfUnresolved
FROM YTDDOIDOL01DGroupDecision
GROUP BY YTDDOIDOL01DGroupDecision.[Type of category],
YTDDOIDOL01DGroupDecision.[Group #]
HAVING (((YTDDOIDOL01DGroupDecision.[Group
#])=[Forms]![frmSelectReports]![frmSub]![lstBoxChoice]));

I have code that produces the correct comparison operator with the string of
the textbox:

=, like '*', =, <>

so my SQL statement in the query looks like this:

HAVING (((GroupDecision.[Group
#])=[Forms]![frmSelectReports]![frmSub]![lstBoxChoice]));

so lets say the string in the textbox were ([Group #] like '*')
... it should work right? and give me every group.

that same textbox could also give me ([Group #] IN('2223', '2224'))

All these strings work if I paste them directly into the query in place of
the parameter. Why don't they work if they're in a textbox as a parameter?

That can not possibly work. A parameter must represent a
value in an expression, not the entire expression or even
part of an expression that includes an operator.

Also, you didn't post the rest of you query's SQL, but I'll
bet you should be using WHERE instead of HAVING. Where is
used to select the data for the query to process, Having is
used to filter out entire groups after the GROUP BY clause
has been completed.

Back to your problem. To filter on expressions, as you cite
above, you have to construct the entire SQL statement in
code. While you're doing that, it's easy to use the
parameter's value instead of asking the query to find it for
you. I'd be happy to take a shot at the code if you'll post
your query's entire SQL statement along with an explanation
of where/how it's being used.

 

[Marshall Barton]

Just as background, we want to be able to show summary data of complaints
that were overturned, upheld, and unresolved for certain groups.
The problem is, for each group that shows, the category is repeated.
The users don't care about the underlying groups in this particular report--
they only care about the each category and how many unresolved, upheld and
overturned there are.

In order to accomplish this, Group # or Group Name must be filtered but not
selected.

I am presently using a listbox to facilitate the user being able to select a
particular group they want to filter for. I've written code that
effectively creates a strFilter variable (and it would work if I passed it to
the docmd.Open report command) but now since they don't want the group # or
name to show, I can't have a query that filters but doesn't select unless I
fill it with something --hence the form textbox field.

The report that I open is called rptYTDDOIDOL_TPA
Yes, it would be great if I could change the underlying query of the report
before it opens on varying conditions.

Here is the entire query:

SELECT YTDDOIDOL01DGroupDecision.[Type of category],
Sum(YTDDOIDOL01DGroupDecision.Overturned) AS SumOfOverturned,
Sum(YTDDOIDOL01DGroupDecision.Upheld) AS SumOfUpheld,
Sum(YTDDOIDOL01DGroupDecision.Unresolved) AS SumOfUnresolved
FROM YTDDOIDOL01DGroupDecision
GROUP BY YTDDOIDOL01DGroupDecision.[Type of category],
YTDDOIDOL01DGroupDecision.[Group #]
HAVING (((YTDDOIDOL01DGroupDecision.[Group
#])=[Forms]![frmSelectReports]![frmSub]![lstBoxChoice]));

I don't understand what the issue with including Group# in
the query's field list (with show checked). The report can
ignore the field so, as far as the report is concerned, it
doesn't matter if it's there or not. BUT, if it is in the
query, then the OpenReport method's WhereCondition argument
(which you say you already know how to do) can be used to
filter the data instead of all this fooling around with the
Having clause (BTW, it could be a Where clause ;-))

Maybe, I'm still not getting the whole picture, but from
what I can see, that's all you need to do.

 

[Guest]

Marsh,

Here are some samples of the results I get in my report


1 ===== when the group field is included and grouped by Category type ====

Overturned Upheld Unresolved Total
(results are only taking one row and omitting the underlying group data)

Benefit Plans 0 1 0 2
Cobra 1 0 0 1


2====== if group is ommitted from selection =======
(results sum correctly)

Overturned Upheld Unresolved Total

Benefit Plans 0 2 0 2
Cobra 1 0 0 1


That's why I'm trying to create a recordset on the fly for my report.
because if the underlying query includes Group information, it gets truncated
in the report when I try to summarize it.

 

[Guest]

After typing out that example...
I realized, all I had to do was bring in the detail, but make it invisible
then sum the detail in the report for the category grouping.

Thanks for your help.

Just as background, we want to be able to show summary data of complaints
that were overturned, upheld, and unresolved for certain groups.
The problem is, for each group that shows, the category is repeated.
The users don't care about the underlying groups in this particular report--
they only care about the each category and how many unresolved, upheld and
overturned there are.

In order to accomplish this, Group # or Group Name must be filtered but not
selected.

I am presently using a listbox to facilitate the user being able to select a
particular group they want to filter for. I've written code that
effectively creates a strFilter variable (and it would work if I passed it to
the docmd.Open report command) but now since they don't want the group # or
name to show, I can't have a query that filters but doesn't select unless I
fill it with something --hence the form textbox field.

The report that I open is called rptYTDDOIDOL_TPA
Yes, it would be great if I could change the underlying query of the report
before it opens on varying conditions.

Here is the entire query:

SELECT YTDDOIDOL01DGroupDecision.[Type of category],
Sum(YTDDOIDOL01DGroupDecision.Overturned) AS SumOfOverturned,
Sum(YTDDOIDOL01DGroupDecision.Upheld) AS SumOfUpheld,
Sum(YTDDOIDOL01DGroupDecision.Unresolved) AS SumOfUnresolved
FROM YTDDOIDOL01DGroupDecision
GROUP BY YTDDOIDOL01DGroupDecision.[Type of category],
YTDDOIDOL01DGroupDecision.[Group #]
HAVING (((YTDDOIDOL01DGroupDecision.[Group
#])=[Forms]![frmSelectReports]![frmSub]![lstBoxChoice]));

I don't understand what the issue with including Group# in
the query's field list (with show checked). The report can
ignore the field so, as far as the report is concerned, it
doesn't matter if it's there or not. BUT, if it is in the
query, then the OpenReport method's WhereCondition argument
(which you say you already know how to do) can be used to
filter the data instead of all this fooling around with the
Having clause (BTW, it could be a Where clause ;-))

Maybe, I'm still not getting the whole picture, but from
what I can see, that's all you need to do.