Report based on complex query generates unwanted prompt 1

[nowickil]

I have a report based on a complex query. The query definition does refer to another query, called 'Contracts/Services'. When I run the query from design view, I get exactly the results I want, with no prompting. So, I went ahead and I created a report based on the complex query. However, when I run the report, I get a prompt with the text 'Contract/Services' and a text input box below it. My options are OK or Cancel. If I click OK,leaving the text input box blank, the query runs and the report is generated with exactly the data I wanted. How can I prevent this dialog box from popping up every time the report runs?

I'm guessing that clicking OK causes the Contracts/Services query results to be generated, and that allows the complex query to complete. But if that's the case, why does it work in query design view but not for the report?

Thanks for the input.

nowickil@integrateddatasystems.com

 

[Falcon99]

My guess is that there is some control on the report referencing "Contract/Services".

Maybe when you were designing the report you dragged a field on the report but later removed it from the query.
Also, I've been caught with an old field in the sorting/grouping options. That will prompt, too, without causing any problems.

As a test, create a quick report based on the query, very simple. See what happens.

Hope this helps
Falcon

 

[nowickil]

I took your advice and created a simple report based on the query. The only field I used in the report was from the Contract/Service query, field name ContractID. I got an error message stating "The wizard was unable to preview the report, possibly because a table needed by the report is exclusively locked. Your report will open in design view."

Then, from design view, I clicked on report preview. This gave me the message "Contract.ContractID could refer to more than one table listed in the FROM clause of your SQL statement."

I clicked on Help and got information stating that I need to fully qualify the name of the field (table.field name). I already had fully qualified the table.field name, so I'm a bit puzzled about why it is not being interpreted correctly. Here's the SQL Statement in question:

SELECT DISTINCTROW Contract.ContractID, Customer.CustName, Contract.Qty, Contract.EndDate, [Contract/Service Query].SiteID, [Contract/Service Query].SiteName, [Contract/Service Query].ServiceID, [Contract/Service Query].ContractID, ProviderRank.[Primary Provider], ProviderRank.ServiceTypeID, Provider.Agency, Provider.Addr1, Provider.Addr2, Provider.City, Provider.State, Provider.Zip, Provider.Fax, Customer.HealthProvider

FROM Customer
INNER JOIN ((([Contract/Service Query]
INNER JOIN ProviderRank
ON ([Contract/Service Query].ServiceID = ProviderRank.ServiceTypeID)
AND ([Contract/Service Query].SiteID = ProviderRank.SiteID))
INNER JOIN Provider
ON ProviderRank.[Primary Provider] = Provider.ProviderID)
INNER JOIN Contract
ON [Contract/Service Query].ContractID = Contract.ContractID)
ON Customer.CustID = Contract.CustID

WHERE (((Contract.EndDate)=Date() Or (Contract.EndDate)>Date()) AND ((ProviderRank.ServiceTypeID)=[ServiceTypeID]));

I can't see where the syntax error would be causing it to appear that " Contract.ContractId could refer to more than one table listed in the FROM clause of your (my) SQL statement".

Any ideas, anyone?

 

[nowickil]

Dear Falcon 99,

I resolved the problem, nad your advice was instrumental. Thank you.

I re-examined my query, and determined I did not need to reference both
Contract. ContractID and Contract/Servcies Query.ContractID. I simplified the query, and went back to testing with a new report. I kept modifying the report, adding fields one at a time until I got to adding a subform on my report. The subform is tied to the report with the ContractID field. In the subform properties, the Link Child and Link Master options were set to [Contract/Services Query].ContractID. I changed that to simply be ContractID (no reference to the Contract/Services Query). With the change I made to the query, this was a sufficient link, and the subform data on the report pulls cleanly and correctly.

I modified the subform properties links on the original report, and it runs clean as a whistle - no prompt!

Thanks so much for your suggestions. You got me on the right road.
Have a great day.