problem printing report with subquery in recordsource

[Jeff B]

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

 

[Duane Hookom]

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

 

[Jeff B]

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

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
--

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