If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
problem printing report with subquery in recordsource
I am trying to show a calculated field (calculated in the query) by using a
subquery. The full query runs correctly when viewed on its own.. The report runs correctly when bound to the query but without trying to show the value returned from the subquery. As soon as I bind a textbox to the subquery the report seems to run but is never previewed on the screen or printed. The query is as follows: SELECT [lclRepAcctDepSumm].[PayType], [lclRepAcctDepSumm].[InvNum], [lclRepAcctDepSumm].[InvDateTime], [lclRepAcctDepSumm].[Type], [lclRepAcctDepSumm].[ContactID], [lclRepAcctDepSumm].[EmpName], [lclRepAcctDepSumm].[Prem], [lclRepAcctDepSumm].[Operating], [lclRepAcctDepSumm].[Direct], [lclRepAcctDepSumm].[CrdDeb], [lclRepAcctDepSumm].[ConEndHdrID], [lclRepAcctDepSumm].[ConEndEndo], [lclRepAcctDepSumm].[PaymentType], iif((SELECT COUNT(*) FROM CONCovOV WHERE lclRepAcctDepSumm.ContactID = ContactID AND CompanyID ='NYAIP' AND lclRepAcctDepSumm.Type='N')0,"X","") AS PP FROM lclRepAcctDepSumm; the problem field is ofcourse 'PP' a sub query. I think the issue is binding the report to the subquery value. I have tried using the raw number returned, or as is posted an iif statment to convert it to an 'X'. If anyone has an answer short of dumping this to a table and binding to the table I would be very gratefull. Version: Access 2000 (Thats what the clients are using). Dev machine: XP, 1 gig memory, 3.0 mgz pentium 4 Thanks in advance Jeff B |
#2
|
|||
|
|||
problem printing report with subquery in recordsource
Generally when I encounter this error is is because I have attempted to sort
or group by the expression containing the subquery. In these cases, I have had to replace the subquery with a very slow domain aggregate function. You can try use: IIf(DCount("*", "CONCovOV", "ContactID = " & [ContactID] & " AND CompanyID ='NYAIP' AND lclRepAcctDepSumm.Type='N'" )0,"X","") AS PP -- Duane Hookom MS Access MVP -- "Jeff B" wrote in message ... I am trying to show a calculated field (calculated in the query) by using a subquery. The full query runs correctly when viewed on its own.. The report runs correctly when bound to the query but without trying to show the value returned from the subquery. As soon as I bind a textbox to the subquery the report seems to run but is never previewed on the screen or printed. The query is as follows: SELECT [lclRepAcctDepSumm].[PayType], [lclRepAcctDepSumm].[InvNum], [lclRepAcctDepSumm].[InvDateTime], [lclRepAcctDepSumm].[Type], [lclRepAcctDepSumm].[ContactID], [lclRepAcctDepSumm].[EmpName], [lclRepAcctDepSumm].[Prem], [lclRepAcctDepSumm].[Operating], [lclRepAcctDepSumm].[Direct], [lclRepAcctDepSumm].[CrdDeb], [lclRepAcctDepSumm].[ConEndHdrID], [lclRepAcctDepSumm].[ConEndEndo], [lclRepAcctDepSumm].[PaymentType], iif((SELECT COUNT(*) FROM CONCovOV WHERE lclRepAcctDepSumm.ContactID = ContactID AND CompanyID ='NYAIP' AND lclRepAcctDepSumm.Type='N')0,"X","") AS PP FROM lclRepAcctDepSumm; the problem field is ofcourse 'PP' a sub query. I think the issue is binding the report to the subquery value. I have tried using the raw number returned, or as is posted an iif statment to convert it to an 'X'. If anyone has an answer short of dumping this to a table and binding to the table I would be very gratefull. Version: Access 2000 (Thats what the clients are using). Dev machine: XP, 1 gig memory, 3.0 mgz pentium 4 Thanks in advance Jeff B |
#3
|
|||
|
|||
problem printing report with subquery in recordsource
Thanks Duane for your response.
Unfortunately I get an error in the PP field when I try your suggestion,I think the query cannot resolve the "[ContactID]" as a variable. Fortunately I found an alternate work around that might be of help to you and the group when this happens. Even though the report fails if bound directly to the posted query, it works fine with a little indirection. I created a query that points to the original query and the report works the way it should have from the beginning. example, if the original query is called query1 create query2 with an SQL statement of SELECT query1.* FROM query1 I'm not happy with having two compiled queries where none were really needed but at least I found a workable solution. Again thanks for all the help Jeff B "Duane Hookom" wrote in message ... Generally when I encounter this error is is because I have attempted to sort or group by the expression containing the subquery. In these cases, I have had to replace the subquery with a very slow domain aggregate function. You can try use: IIf(DCount("*", "CONCovOV", "ContactID = " & [ContactID] & " AND CompanyID ='NYAIP' AND lclRepAcctDepSumm.Type='N'" )0,"X","") AS PP -- Duane Hookom MS Access MVP -- "Jeff B" wrote in message ... I am trying to show a calculated field (calculated in the query) by using a subquery. The full query runs correctly when viewed on its own.. The report runs correctly when bound to the query but without trying to show the value returned from the subquery. As soon as I bind a textbox to the subquery the report seems to run but is never previewed on the screen or printed. The query is as follows: SELECT [lclRepAcctDepSumm].[PayType], [lclRepAcctDepSumm].[InvNum], [lclRepAcctDepSumm].[InvDateTime], [lclRepAcctDepSumm].[Type], [lclRepAcctDepSumm].[ContactID], [lclRepAcctDepSumm].[EmpName], [lclRepAcctDepSumm].[Prem], [lclRepAcctDepSumm].[Operating], [lclRepAcctDepSumm].[Direct], [lclRepAcctDepSumm].[CrdDeb], [lclRepAcctDepSumm].[ConEndHdrID], [lclRepAcctDepSumm].[ConEndEndo], [lclRepAcctDepSumm].[PaymentType], iif((SELECT COUNT(*) FROM CONCovOV WHERE lclRepAcctDepSumm.ContactID = ContactID AND CompanyID ='NYAIP' AND lclRepAcctDepSumm.Type='N')0,"X","") AS PP FROM lclRepAcctDepSumm; the problem field is ofcourse 'PP' a sub query. I think the issue is binding the report to the subquery value. I have tried using the raw number returned, or as is posted an iif statment to convert it to an 'X'. If anyone has an answer short of dumping this to a table and binding to the table I would be very gratefull. Version: Access 2000 (Thats what the clients are using). Dev machine: XP, 1 gig memory, 3.0 mgz pentium 4 Thanks in advance Jeff B |
Thread Tools | |
Display Modes | |
|
|