subquery without using EXISTS word (?)

  • Thread starter Thread starter Mark Kubicki
  • Start date Start date
M

Mark Kubicki

I am trying to open a report with the following code:

strSQL = SELECT SheetsToPrint.* FROM SheetsToPrint WHERE
SheetsToPrint.Type IN('TA', 'TAA', 'TD'); 'strSQL is built with code not
shown here
DoCmd.OpenReport stDocName, acPreview, , strSQL

the report itself has a query as its recordsource which reads (lengthy, I
know, but that's what I inherited...):
(the recordsource by itself does work)

SELECT
IIf(InStr([Type],"-")=0,Len(AllPagesToPrint!type),InStr([Type],"-")-1)
AS order1,
IIf(InStr([Type],"EM")=0,[Type],Replace([Type],"EM","0")) AS order2,
AllPagesToPrint.Type, AllPagesToPrint.printorder1,
IIf(AllPagesToPrint!CatalogSheetLink Is
Null,"",Right(AllPagesToPrint!CatalogSheetLink,Len(AllPagesToPrint!CatalogSheetLink)-1))
_
AS CatalogSheetLink, ProjectnInfo.ProjectName,
ProjectnInfo.currentissuetitle, ProjectnInfo.currentissuedate _
FROM AllPagesToPrint, ProjectnInfo
WHERE (((Len([AllPagesToPrint]![CatalogSheetLink]))>1))
ORDER BY
IIf(InStr([Type],"-")=0,Len(AllPagesToPrint!type),InStr([Type],"-")-1),
IIf(InStr([Type],"EM")=0,[Type],Replace([Type],"EM","0")),
AllPagesToPrint.printorder1;

I am getting this error on run
' You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's FROM clause... '

Any suggestions?, I am totally at a loss

Many thanks in advance,
Mark
 
The strSQL in
DoCmd.OpenReport stDocName, acPreview, , strSQL
must be only the WHERE CLAUSE. Typically I would expect to see just:
strSQL = " [Type] IN('TA', 'TAA', 'TD') "
If your report record source doesn't have the [Type] field in its field list
then you can't use it in the WHERE CONDITION.
 
Back
Top