Solved Subreport Not showing up in Print Preview (1 Viewer)

Weekleyba

Registered User.
Local time
Yesterday, 23:58
Joined
Oct 10, 2013
Messages
586
I'm trying to find a work around for this problem.
I have a report with subreports.
When the query feeding the subreports return null, the supreport will not show up in Print Preview.
It will however show up in Report View.

I read somewhere to use a union query for the record source for the supreports to return something instead of null, however I'm not sure how to accomplish that.
How can I do this?

REPORT VIEW
1582232647876.png


PRINT PREVIEW
1582232678873.png
 

isladogs

MVP / VIP
Local time
Today, 04:58
Joined
Jan 14, 2017
Messages
18,164
Have you tried using the Nz function with the false part set to zero?
 

Weekleyba

Registered User.
Local time
Yesterday, 23:58
Joined
Oct 10, 2013
Messages
586
I have inside the individual subreports, like shown below.
1582234840747.png
 

Weekleyba

Registered User.
Local time
Yesterday, 23:58
Joined
Oct 10, 2013
Messages
586
Attached is the db.
Open it, select a Location "Belcourt Service Unit", then click Open Report.
It opens in Print Preview. You can open it in Report View as well to see the difference.
I want to the Print Preview to look like the Report View, since I will be printing this out.
 

Attachments

  • DFM Funding Database 9.zip
    330.5 KB · Views: 137

TraceSL

Registered User.
Local time
Today, 00:58
Joined
Sep 12, 2017
Messages
51
I built complex reports which contain many sub reports to bypass the issue I linked them on a core data value that's common between both they work in print and report views.
 

Weekleyba

Registered User.
Local time
Yesterday, 23:58
Joined
Oct 10, 2013
Messages
586
Trace can you explain a little further.
My subreports are linked (Master & Child) via a field that is a 'short text'.
Are you saying if it was a 'number' field it would work?
 

TraceSL

Registered User.
Local time
Today, 00:58
Joined
Sep 12, 2017
Messages
51
The master child link is a field that's always has a value like year or company code and is consistent.
Check the default view on the sub reports, open the sub report in design view and verify the default view, I noticed some of mine are set to report preview and others to print preview. However, I believe when I created the sub reports I set the preference to print preview for all of them so I changed them back.
I would check each sub report default preview options to ensure they match!
 

Weekleyba

Registered User.
Local time
Yesterday, 23:58
Joined
Oct 10, 2013
Messages
586
That did not work for me. I changed all default views to Report View no go, then changed all to Print Preview, still no go.
My reports are linked by a common short text field. example: 45211SDH000000
Any other ideas?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:58
Joined
Oct 29, 2018
Messages
21,322
I read somewhere to use a union query for the record source for the supreports to return something instead of null, however I'm not sure how to accomplish that.
How can I do this?
So, going by your idea, I changed the EQUIPMENT subreport to use a UNION query. Please take a look. Hope it helps...
 

Attachments

  • DFM Funding Database 9.zip
    330.5 KB · Views: 152

Weekleyba

Registered User.
Local time
Yesterday, 23:58
Joined
Oct 10, 2013
Messages
586
Exactly what I needed!
I would have never figured out "UNION select [Forms]![Main_F]![Combo0],0,0,0,0,0"
I thought I had to have another table to do a union query.

General question: How far down does this Access rabbit hole go? Not sure I'll ever reach the bottom. 🤪
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:58
Joined
Oct 29, 2018
Messages
21,322
Exactly what I needed!
I would have never figured out "UNION select [Forms]![Main_F]![Combo0],0,0,0,0,0"
I thought I had to have another table to do a union query.

General question: How far down does this Access rabbit hole go? Not sure I'll ever reach the bottom. 🤪
Hi. Glad to hear that works for you. It goes down as deep as you continue to dig. So, don't try to dig too much! Good luck!
 

TraceSL

Registered User.
Local time
Today, 00:58
Joined
Sep 12, 2017
Messages
51
Good luck sounds like you may have a resolution to this issue.
 

Weekleyba

Registered User.
Local time
Yesterday, 23:58
Joined
Oct 10, 2013
Messages
586
DBguy,
After I started to implement the solution, I ran into another problem. When there is data in the fields, the Union query will always give me a line of zeros and with the data record, thereby giving me two records in the query. This then shows up in the report with two lines, the 0 and the data. See below.
1582298474190.png

Is there a way to augment the Union query SQL, to filter out the 0 when there is data in there?

Code:
UNION select [Forms]![Main_F]![Combo0],0,0,0,0,0 from [IHS - Allowance Status by Project and Location];

I've tried creating another query with the Union query and summing but, I have to drop out all the fields except the Funds Available and Allowanced fields to get it to work. But then I can't use the new query for the Record Source since it doesn't have the Budget Activity Program in it.

Any thoughts on this?

Side note: I'm a bit surprised that Access has this problem. Report View looks great but, we have to jump through a bunch of hoops to get Print Preview to look the same???
 

Weekleyba

Registered User.
Local time
Yesterday, 23:58
Joined
Oct 10, 2013
Messages
586
I could change the Union query to:
Code:
UNION select [Forms]![Main_F]![Combo0],7206451201,0,0,"0J037020200DA0",2020 from [IHS - Allowance Status by Project and Location];
And then create another query and sum. That would work however when the year changes to 2021, I'll have a problem.

I though I could change the 2020 to:
Code:
UNION select [Forms]![Main_F]![cboLocation1],7206451201,0,0,"0J037020200DA0",  [IHS - Allowance Status by Project and Location].Fund, Mid([Fund],7,4) AS FYFund
FROM [IHS - Allowance Status by Project and Location]
But it gives me a error "The number of columns in the two selected queries of a union query do not match."

Any thoughts?

Here's the whole union query for reference.
Code:
SELECT [IHS - Allowance Status by Project and Location].[IHS - Location], [IHS - Allowance Status by Project and Location].[Budget Activity Program], Sum([IHS - Allowance Status by Project and Location].[Funds Available]) AS [SumOfFunds Available], Sum([IHS - Allowance Status by Project and Location].[Total Allowance Issued]) AS [SumOfTotal Allowance Issued], [IHS - Allowance Status by Project and Location].Fund, Mid([Fund],7,4) AS FYFund
FROM [IHS - Allowance Status by Project and Location]
GROUP BY [IHS - Allowance Status by Project and Location].[IHS - Location], [IHS - Allowance Status by Project and Location].[Budget Activity Program], [IHS - Allowance Status by Project and Location].Fund, Mid([Fund],7,4)
HAVING ((([IHS - Allowance Status by Project and Location].[IHS - Location])=[Forms]![Main_F]![cboLocation1]) AND (([IHS - Allowance Status by Project and Location].[Budget Activity Program])=7206451201) AND ((Mid([Fund],7,4))=IIf(Month(Date())<10,Year(Date()),Year(Date())+1)));
UNION select [Forms]![Main_F]![cboLocation1],7206451201,0,0,"0J037020200DA0",  [IHS - Allowance Status by Project and Location].Fund, Mid([Fund],7,4) AS FYFund
FROM [IHS - Allowance Status by Project and Location]
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:58
Joined
Oct 29, 2018
Messages
21,322
DBguy,
After I started to implement the solution, I ran into another problem. When there is data in the fields, the Union query will always give me a line of zeros and with the data record, thereby giving me two records in the query. This then shows up in the report with two lines, the 0 and the data.
Hi. I couldn't duplicate this in your sample db probably because there are no options from the dropdown with matching data, but I tried to change it. See if this helps...
 

Attachments

  • DFM Funding Database 9 (2).zip
    331.1 KB · Views: 112

Weekleyba

Registered User.
Local time
Yesterday, 23:58
Joined
Oct 10, 2013
Messages
586
I cleaned the SQL up a bit getting rid of all the superfluous references to the table.

Code:
SELECT [IHS - Location], [Budget Activity Program], Sum([Funds Available]) AS [SumOfFunds Available], Sum([Total Allowance Issued]) AS [SumOfTotal Allowance Issued], Fund, Mid([Fund],7,4) AS FYFund
FROM [IHS - Allowance Status by Project and Location]
GROUP BY [IHS - Location], [Budget Activity Program], [Fund], Mid([Fund],7,4)
HAVING ((([IHS - Location])=[Forms]![Main_F]![cboLocation1]) AND (([Budget Activity Program])=7206451201) AND ((Mid([Fund],7,4))=IIf(Month(Date())<10,Year(Date()),Year(Date())+1)));
UNION select [Forms]![Main_F]![cboLocation1],7206451201,0,0,"0J037020200DA0",2020 AS FYFund
FROM [IHS - Allowance Status by Project and Location]
 

Weekleyba

Registered User.
Local time
Yesterday, 23:58
Joined
Oct 10, 2013
Messages
586
Well, I think it needs one more tweek but I can't figure it out.
Your tweek of "SELECT TOP 1 * FROM EQUIP_FED_CurrentFY_Q;", selects the top row, which happens to be the 0 row and the row with the data.
See below.
From the query:
1582303537015.png

(Note this only happens when you select the "Other" in the search criteria and "Division of Facility Management")

I tried to changing the query to DESC but it didn't work.
If we can get the query to put the largest values on the top, then the "SELECT TOP 1" would work...I think.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:58
Joined
Oct 29, 2018
Messages
21,322
Hi. I don't know how you tried the DESC part, but try it this way:
SQL:
SELECT TOP 1 * FROM EQUIP_FED_CurrentFY_Q ORDER BY FYFund DESC
 

Weekleyba

Registered User.
Local time
Yesterday, 23:58
Joined
Oct 10, 2013
Messages
586
Nice!
I was trying to add the DESC to the union query SQL.
Probably did it wrong....
Thanks for the assist! Again.
 

Users who are viewing this thread

Top Bottom