Sub-report not showing up

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report that has 2 sub-reports within the body of the report. The 2
sub-reports are pulling from cross-tab queries. The problem is, the second
sub-report (which is pulling quarterly financial data) is not coming over if
the quarter $$ amount is null. The headings of the sub are Qtr 1, Qtr 2, Qtr
3, and Qtr 4. As long as the fields have a value in them, when the sub
report is pulled with the overall report it shows the sub-report at the
bottom where it's supposed to be. However, if, for example, Qtr 2 has no $$,
the whole subreport disappears and the error pops up "The Microsoft Jet
database engine does not recognize '[Qtr 2]' as a valid name or expression."

Below is the SQL expression for this particular sub-report:

TRANSFORM Sum([FY 2006 Forecast].BookAmount) AS SumOfBookAmount
SELECT [FY 2006 Forecast].BusAreaID, Sum([FY 2006 Forecast].BookAmount) AS
[Total Of BookAmount]
FROM [FY 2006 Forecast]
GROUP BY [FY 2006 Forecast].BusAreaID
PIVOT "Qtr " & Format([BookDate],"q");


PLEASE HELP!
Thanks!!!!!!!!!!!!
 
Open your crosstabs in design view and find the Column Headings property.
Enter each possible column heading value into this property:
"Qtr 1";"Qtr 2";"Qtr 3";"Qtr 4"
 
Duane,

Oh my gosh, it worked!!!!!!!!!!!!!!!!!!!

Thank you soooooooooo much!!!!
--
Cyndi


Duane Hookom said:
Open your crosstabs in design view and find the Column Headings property.
Enter each possible column heading value into this property:
"Qtr 1";"Qtr 2";"Qtr 3";"Qtr 4"

--
Duane Hookom
MS Access MVP


Cyndi said:
I have a report that has 2 sub-reports within the body of the report. The
2
sub-reports are pulling from cross-tab queries. The problem is, the
second
sub-report (which is pulling quarterly financial data) is not coming over
if
the quarter $$ amount is null. The headings of the sub are Qtr 1, Qtr 2,
Qtr
3, and Qtr 4. As long as the fields have a value in them, when the sub
report is pulled with the overall report it shows the sub-report at the
bottom where it's supposed to be. However, if, for example, Qtr 2 has no
$$,
the whole subreport disappears and the error pops up "The Microsoft Jet
database engine does not recognize '[Qtr 2]' as a valid name or
expression."

Below is the SQL expression for this particular sub-report:

TRANSFORM Sum([FY 2006 Forecast].BookAmount) AS SumOfBookAmount
SELECT [FY 2006 Forecast].BusAreaID, Sum([FY 2006 Forecast].BookAmount) AS
[Total Of BookAmount]
FROM [FY 2006 Forecast]
GROUP BY [FY 2006 Forecast].BusAreaID
PIVOT "Qtr " & Format([BookDate],"q");


PLEASE HELP!
Thanks!!!!!!!!!!!!
Click to expand...
Click to expand...
 
Don't act so surprised.....

--
Duane Hookom
MS Access MVP
--

Cyndi said:
Duane,

Oh my gosh, it worked!!!!!!!!!!!!!!!!!!!

Thank you soooooooooo much!!!!
--
Cyndi


Duane Hookom said:
Open your crosstabs in design view and find the Column Headings property.
Enter each possible column heading value into this property:
"Qtr 1";"Qtr 2";"Qtr 3";"Qtr 4"

--
Duane Hookom
MS Access MVP


Cyndi said:
I have a report that has 2 sub-reports within the body of the report.
The
2
sub-reports are pulling from cross-tab queries. The problem is, the
second
sub-report (which is pulling quarterly financial data) is not coming
over
if
the quarter $$ amount is null. The headings of the sub are Qtr 1, Qtr
2,
Qtr
3, and Qtr 4. As long as the fields have a value in them, when the sub
report is pulled with the overall report it shows the sub-report at the
bottom where it's supposed to be. However, if, for example, Qtr 2 has
no
$$,
the whole subreport disappears and the error pops up "The Microsoft Jet
database engine does not recognize '[Qtr 2]' as a valid name or
expression."

Below is the SQL expression for this particular sub-report:

TRANSFORM Sum([FY 2006 Forecast].BookAmount) AS SumOfBookAmount
SELECT [FY 2006 Forecast].BusAreaID, Sum([FY 2006 Forecast].BookAmount)
AS
[Total Of BookAmount]
FROM [FY 2006 Forecast]
GROUP BY [FY 2006 Forecast].BusAreaID
PIVOT "Qtr " & Format([BookDate],"q");


PLEASE HELP!
Thanks!!!!!!!!!!!!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top