Adding blanks to complete report

[SirTKC]

Hi there,

I don't know if its possible but I would like to add "blank" records in order to fill the rest of the details section of the report.

Lets just say for example that I have room for 20 lines in my report. But have only 13 records in my query results that will fill that report. I'dd like the printout to show 13 strings + 12 blanks as a "grid".

Thing is, there is some results that returns 29 strings. So the same rule has to apply to the second page. The first page will be filled with 25 strings and the next one with 4 strings + 21 blanks.

And so on. I thought it would be an easy thing to add "dummy records", but apparently not... At least for me ! :-(

Is it possible using VBA ?

 

[theDBguy]

Hi. Adding dummy records using a UNION query would be my first thought as well.

 

[isladogs]

This has come up several times in the past. If you do a forum search for blank lines in report you will get lots of hits. You could also look at the similar threads section at the bottom of this page.

Here's one solution: https://www.access-programmers.co.uk/forums/showthread.php?t=301963&highlight=Add+blank+lines+report

 

[SirTKC]

Hi. Adding dummy records using a UNION query would be my first thought as well.

Actualy I came across this one but I don't think it would fit what I need.

I really need to add blank strings instead of crossing data.

Unless I really missed something.

 

[theDBguy]

Actualy I came across this one but I don't think it would fit what I need.

I really need to add blank strings instead of crossing data.

Unless I really missed something.

Hi. Maybe not but let me explain what I was thinking, and you can say for sure whether it applies or not. So, for simplicity's sake, let's say you have a one-page report with room for 22 lines/records. So, I was saying create a table with 22 black records in it. You would then UNION this table with your actual table using the SELECT TOP 22 keyword to limit the result to only 22 records. For example:

SELECT TOP 22 ID FROM (SELECT ID FROM YourTable UNION ALL SELECT ID FROM EmptyTable)

What that does is if your primary table has 22 records, then none of the empty records get included in the report. However, if your primary table has less than 22 records, then the rest is filled-in by the empty records - but only up to 22 records. Now, whether we can apply this idea to multi-page reports, I'll have to try it out to see.
Hope it makes sense...

 

[SirTKC]

Hi. Maybe not but let me explain what I was thinking, and you can say for sure whether it applies or not. So, for simplicity's sake, let's say you have a one-page report with room for 22 lines/records. So, I was saying create a table with 22 black records in it. You would then UNION this table with your actual table using the SELECT TOP 22 keyword to limit the result to only 22 records. For example:

SELECT TOP 22 ID FROM (SELECT ID FROM YourTable UNION SELECT ID FROM EmptyTable)

What that does is if your primary table has 22 records, then none of the empty records get included in the report. However, if your primary table has less than 22 records, then the rest is filled-in by the empty records - but only up to 22 records. Now, whether we can apply this idea to multi-page reports, I'll have to try it out to see.
Hope it makes sense...

Yes, absolutely, I am starting to get the point now. Thing is I never thought of that approach. I'm getting through the link Isladogs posted here. It does the job. But seem very complexe. If it's what it takes, let's try it !

Lemme try your suggestion and get back to you with this. To see if am a good padawan ;-)

 

[theDBguy]

Okay, thanks. Good luck. I won’t be able to give it a try until tomorrow, so I hope you get it.

 

[deletedT]

I have a lot of different reports each one with different possible lines per page.
When a user clicks print button, A vba code copies search result to a Dummy table and adds necessary blank records. Then opens the report and sets the record source of the report to the dummy table.

I never had thought of a UNION solution. It seems smarter than what I do.

 

[SirTKC]

All right... I feel am close to the solution...

I have created my union query in a module. It works but the result is upside down (meaning that the blank lines are first and then the data). And my report is missing the header on the first page for all results returned that exceeds one page.


Am probably missing just an ORDER BY in my data assembly...

Let me know what you think

Here is my Union


Public Function fncMyRecordSource(iTop As Integer) As String

Dim sSQL1 As String
Dim sSQL2 As String


sSQL2 = "SELECT 0 As Expr1, [0_WO].WONo, [0_WO].ShipTo, [0_WO].ShipName, [0_WO].ShipSubName, [0_WO].ShipAddress, [0_WO].ShipCity, [0_WO].ShipState, [0_WO].ShipZipCode, [0_WO].SerialNo, [0_WO].Make, [0_WO].UnitNo, [0_WO].Model, [0_WOParts].Qty, [0_WOParts].PartNo, [0_WOParts].Description " & _
" FROM 0_WO INNER JOIN 0_WOParts ON [0_WO].WONo = [0_WOParts].WONo " & _
" WHERE ((([0_WO].WONo)=[Forms]![F_Req_Pieces]![SearchReq]));"

sSQL1 = "SELECT TOP " & iTop & " Expr1, '' AS WONo, '' AS ShipTo, '' AS ShipName, '' AS ShipSubName, '' AS ShipAddress, '' AS ShipCIty, '' AS ShipState, '' AS ShipZipCode, '' AS SerialNo, '' AS Make, '' AS UnitNo, '' AS Model, '' AS Qty, '' AS PartNo, '' AS Description " & _
"FROM zzTable"

fncMyRecordSource = sSQL1 & " Union All " & sSQL2

End Function

 

[theDBguy]

Hi. Glad to hear you're making good progress. Reports have their own ORDER BY facility. Otherwise, it just ignores any ORDER BY clause you add to the SQL statement in the Record Source. So, try applying a sort order using the Report's feature.

 

[SirTKC]

Hi. Glad to hear you're making good progress. Reports have their own ORDER BY facility. Otherwise, it just ignores any ORDER BY clause you add to the SQL statement in the Record Source. So, try applying a sort order using the Report's feature.

This is the first thing I've tried. However, the sorting has to be made at THE SQL SELECT. Otherwise am losing all the information in the header.

In the previous version of my creation, I had en subform acting as details data in the details section. But in order to fit the example posted here, I went with one single record source for the whole report. Now I need to feed the header as well as the detail section.

 

[theDBguy]

Hi. Take a look at the sample SQL statement I posted earlier. Try doing it that way. You could try adding a sort order in the union query if you're losing the correct data. Otherwise, you might consider posting a sample db, so we can help you tweak it a little bit.

 

[SirTKC]

Hi. Take a look at the sample SQL statement I posted earlier. Try doing it that way. You could try adding a sort order in the union query if you're losing the correct data. Otherwise, you might consider posting a sample db, so we can help you tweak it a little bit.

Thanks DBguy, unfortunately, I can't post a sample since the data is an SQL back-end.

Thing is as soon as I remove the dummy table from my Query, everything work just fine. As soon as I reunify the qeries as UNION, for a reason I just can't figure, it sorts it blanks first and then the strings. And by doing so, it wipes my report header.

Otherwise it does what it 's supposed to do.

 

[theDBguy]

Thanks DBguy, unfortunately, I can't post a sample since the data is an SQL back-end.

Thing is as soon as I remove the dummy table from my Query, everything work just fine. As soon as I reunify the qeries as UNION, for a reason I just can't figure, it sorts it blanks first and then the strings. And by doing so, it wipes my report header.

Otherwise it does what it 's supposed to do.

Hi. Are you able to create a demo using local tables? Otherwise, I'm not sure how else to help you without seeing exactly what you're doing. I said to try it this way, but you did not specify if you did or not.

SELECT * FROM 
(SELECT * FROM Table1 
UNION ALL 
SELECT * FROM Table2) 
ORDER BY FieldName (add DESC if necessary)

 

[isladogs]

Thanks DBguy, unfortunately, I can't post a sample since the data is an SQL back-end.

Yes you can.
Make a copy of the FE. Right click on the table(s) for this issue and select Convert to local table. Next delete all objects not relevant to this issue, compact and zip.

 

[SirTKC]

Yes you can.
Make a copy of the FE. Right click on the table(s) for this issue and select Convert to local table. Next delete all objects not relevant to this issue, compact and zip.

You're right, I've managed to create a copy and purge any useless data (the original database is 37Gb...)

Please find the example of my doing in the attached file

 

[isladogs]

OK - had a quick look at this.
Remember that the sorting in a report is done on the report itself
I've done the following

with this result

Check if its what you require. I haven't looked at any of the code used.

 

[theDBguy]

Hi. Not sure if this is what you're looking for, but I made some quick adjustments.

 

[SirTKC]

Well done !

Today has been a great day ! I've learned a lot

thanks to you guys ! it works like a charm.

Union is the way to go and sorting the form did it.

 

[SirTKC]

Hi. Not sure if this is what you're looking for, but I made some quick adjustments.

I took a closer look at what you did and wow, you really cleaned it.

Much better approach !

Bravo !!