Prev: Create a seperate PDF Report for each company
Next: Access 2007 Close button disappears..
From: Mark Kubicki on 18 Nov 2009 10:35
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


From: Duane Hookom on 18 Nov 2009 12:00
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.

--
Duane Hookom
Microsoft Access MVP


"Mark Kubicki" wrote:

> 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
>
>
> .
>
 | 
Pages: 1
Prev: Create a seperate PDF Report for each company
Next: Access 2007 Close button disappears..