report/parameter query

Topic

New Reply

(Access 2000/Windows 98)

I have a report whose record source is a query that is made up of several other queries. It’s slow, but it works for what I need, which is to combine information from several tables so that I end up with 1 row for each of my “positions”.

In the process of trying to write some of the queries to make them run faster, I added a field to the output that was the “sum” of a field available from a previous query. Now when I use that field in the report, it comes up with an “Enter parameter value” box when I preview the report.

If I open the query that is the record source for the report, I don’t get any errors or prompts, and all information is valid.

I’ve gone back through from the beginning but can’t find anywhere this would be coming from.

Is there a special way I have to reference this in the report itself since it is not a native field from a table, but an expression created in one of the queries with the ‘sum’ function?

I have another field that was already being used that is identical to how this was one created, and don’t have any problem with it. The only difference I can see with it is the control source for that field isn’t set directly to that field, but set through an IIF. But it does work fine.

Any insight, or suggestion on where I could look for more help would be greatly appreciated. I’m using Access 2000. I’ve checked Access’ help about the parameter queries, but their possible culprits don’t fit.

thanks again.

Reply | Quote

Replies

What does this mean?[indent]

---

added a field to the output that was the “sum” of a field available from a previous query

---

[/indent]If you used a Sum aggregate on the field in a totals query and didn’t supply a custom expression name, the name will be returned as something like SumOfMyFieldName. If your report is trying to reference MyFieldName, the query engine won’t find it under the described scenario and will assume you’re trying to reference a parameter.

Reply | Quote

Thanks for responding. I’ll try and explain a little better.

I already was using a totals query that had an expression I named “SumOfYL” which is the sum of a field named salary_expended. Within in the same totals query (could that be my problem??) I added another expression called “SumOfSalaryEn” which is the sum of a field called salary.

Originally, I allowed it to name it SumOfSalary as you said,but I was using “SumOfSalary” on the report and not just “salary” to reference it. When I changed the expression name to “SumOfSalaryEn” I still get the parameter box when I try to preview the report.

I think I mentioned before the field that I had summed originally works fine in the report. I believe I am doing exactly the same thing for this other field, yet I get the parameter box.

I hope that makes sense. This isn’t an example of a typical query that I work with, because they don’t ususally involve all of these other queries as tables. If you don’t have any suggestions, I’ll just put it back to a dlookup. Thanks for any input or suggestions you may be able to provide.

Reply | Quote

I just found my problem. As I was working on something else I found that I was changing the report’s record source in the “on open” dependent upon a field on a parameter form. So even tho I could see the correct results when I ran the query, that query wasn’t the one being used when the report formatted. No wonder it didn’t know what field I was talking about.

Sorry to waste your time. I tried to look everywhere before posting, but just didn’t catch that one. Thanks for your help anyhow.

Reply | Quote