Using a report field in a query where clause

[meckeard]

Hi All,

I have a query as follows:

select sum(NumOfLines) as TTLLines
from tblTable
where tblTable.Billing_ID = rptReport.FieldName

This query should look at a hidden control on a report (called rptReport) and use it in the where clause.

Now, when I run the query and provide the Billing_ID manually, it works fine. But when I run the report, it prompts me with a message box with the query name as the text?!?

I know the Billing_ID value is in the control on the form. I can see that. And the query runs fine manually. But not in the report.

Is it NOT possible to reference a control value on a report at run-time?

Thanks,
Mark

 

[LarryDeLaruelle]

Mark:

If the query is the source of the report you are referencing then, no, you can't do it that way.

Essentially you have set up a circular reference -- the query needs a value from the report, the report won't open until the query runs and the query won't run until it gets the value from the open report.

You said: "I know the Billing_ID value is in the control on the form."

If you have the value on a form from which you call the report (I'm assuming here), then in the query reference the control on the form that contains the value.

HTH

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[meckeard]

Larry,

I guess more details might help.

I have a report that has 1 query that gets 99% of all the data. It can't return 100% because when I used a sub-select (to get the sum value I originally listed) it bombed when the report ran. Here was the error:

"Multi-level GROUP BY clause not allowed in a subquery"

I think it's happening because I am breaking the report by a field (in this case, on vendor name). So I think the report is doing a "group by" and the sub-select causes a problem.

So, as a work around, I decided to add a new control that links to the second query for my sum value. I thought that as the report ran and started to write each line, it would place the Billing_ID in the first (of 8) controls. And then, by the time it hit the last control (the sum value control), it would be able to access the Billing_ID from the first control.

From what you said, that's not possible.

With this said, what can I do?

Thanks,
Mark