Blank Space on report

**kostakenny** · 11-18-2022, 02:16 PM

Here is another sample comments page. Notice how there is no space between comments 1-3 and 1-7 even though comments 1-4, 1-5, and 1-6 are missing.

**June7** · 11-18-2022, 02:19 PM

Well, that answers my question. Fields are arranged vertically.

Did you try the suggestion from post #5?

How is each comment number generated?

If you normalized these fields as records in a related table, your requirement could easily be accomplished.

I reinforce earlier suggestion you provide db for analysis. Follow instructions at bottom of my post.

**Micron** · 11-18-2022, 02:27 PM

Well, no doubt you're not going to want to know this, but the primary cause of your issue is that you designed your table like a spreadsheet. Around here, that has various names like "committing spreadsheet" or "Excelitis" and the like. When you do that, regardless of whether or not a field has a value it is part of a record. You might be able to fudge things to hide empty controls, but eventually, that's how you build a Rube Goldberg machine. The proper fix is to normalize your tables so that if there's no pertinent data, there's no record (in your case, no blank notes). Maybe take a gander at the subject of db normalization:

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

**kostakenny** · 11-18-2022, 02:47 PM

I did not create this database, I inherited it. I'm just trying to accomplish this one task.

**Micron** · 11-18-2022, 02:59 PM

I reinforce earlier suggestion you provide db for analysis.

Me too. Not easy to cure such an issue when you can't see the patient.

**Welshgasman** · 11-18-2022, 03:12 PM

So try post 5, else all I can think of is a bunch of union queries for each field, which would be messy, no doubt.

**kostakenny** · 11-18-2022, 03:16 PM

Here is a watered down version of my database. The one report that is there, the "COMMENTS" report, is my attempt at a solution to my problem. I know it is a pretty bad one but it is the best I could come up with. I am an absolute novice when it comes to access.

**June7** · 11-18-2022, 03:47 PM

Well, that long conditional expression is one way.

Micron's suggestion for setting Height property of textboxes is another.

And Welshgasman's idea for a UNION query is another. This would essentially rearrange fields to a normalized table structure that could then be joined to other data or used as source for a subreport. Blank fields would be excluded.

Code:

SELECT WIN, 1 AS Seq, "1-1:   " & [TOP OF WALL OUTWARD NOTE] AS Data FROM [Structural Condition Assessment] WHERE NOT [TOP OF WALL OUTWARD NOTE] IS NULL
UNION SELECT WIN, 2, "1-2:   " & [TOP OF WALL INWARD NOTE] FROM [Structural Condition Assessment] WHERE NOT [TOP OF WALL INWARD NOTE] IS NULL
UNION SELECT WIN, 3, "1-3:   " & [BULGING/WARPING OF WALL NOTE] FROM [Structural Condition Assessment] WHERE NOT [BULGING/WARPING OF WALL NOTE] IS NULL
UNION SELECT WIN, 4, "1-4:   " & [TOP OF WALL ALIGNED NOTE] FROM [Structural Condition Assessment] WHERE NOT [TOP OF WALL ALIGNED NOTE] IS NULL
UNION SELECT WIN, 5, "1-5:   " & [TIEBACKS NOTE] FROM [Structural Condition Assessment] WHERE NOT [TIEBACKS NOTE] IS NULL
UNION SELECT WIN, 6, "1-6:   " & [SETTLEMENT OF WALL NOTE] FROM [Structural Condition Assessment] WHERE NOT [SETTLEMENT OF WALL NOTE] IS NULL
UNION SELECT WIN, 7, "1-7:   " & [EXPANSION/CONSTRUCTION JOINTS NOTE] FROM [Structural Condition Assessment] WHERE NOT [EXPANSION/CONSTRUCTION JOINTS NOTE] IS NULL
UNION SELECT WIN, 8, "1-8:   " & [DISPLACED LASRGE STONE NOTE] FROM [Structural Condition Assessment] WHERE NOT [DISPLACED LASRGE STONE NOTE] IS NULL
UNION SELECT WIN, 9, "1-9:   " & [DISPLACE SMALL STONE NOTE] FROM [Structural Condition Assessment] WHERE NOT [DISPLACE SMALL STONE NOTE] IS NULL
UNION SELECT WIN, 10, "1-10:   " & [DISPLACED BLOCKS/BRICKS NOTE] FROM [Structural Condition Assessment] WHERE NOT [DISPLACED BLOCKS/BRICKS NOTE] IS NULL
UNION SELECT WIN, 11, "1-11:   " & [DIAGONAL CRACKS NOTE] FROM [Structural Condition Assessment] WHERE NOT [DIAGONAL CRACKS NOTE] IS NULL
UNION SELECT WIN, 12, "1-12:   " & [VERTICAL CRACKS NOTE] FROM [Structural Condition Assessment] WHERE NOT [VERTICAL CRACKS NOTE] IS NULL;

Sorting on the comment number will not be correct because alpha sort rules apply. 1-11 will come before 1-2. Either change the number to be like 1-02 or use calculated Seq to dictate sorting. UNION query will open automatically sorted by left to right field order. One drawback is that UNION query can perform slowly with very large dataset. I have used it without issue for dataset of about 65,000 records. There is no query designer for UNION. Must type or copy/paste into SQLView of query builder. There is a limit of 50 SELECT lines.

**kostakenny** · 11-18-2022, 03:56 PM

June7, I appreciate your reply, but I need this to be explained to me like I'm 5 years old

. Where do I even paste this code, and will it produce a report like the one I have started?

Thank you in advance, and I'm sorry if my lack of knowledge is frustrating to you are anyone else involved.

**June7** · 11-18-2022, 04:13 PM

I already edited my previous post, probably while you were reading. Review again.

**Micron** · 11-18-2022, 04:23 PM

Methinks textbox height was WGM's idea so giving credit where it is due.
@kostakenny, while the union query might work, IMO it's just another brick in the obstruction wall; i.e. just another fudge factor that could be eliminated by table design. I get the point about inheritance and the desire to just put this to bed until next time. Trouble with that is, the more fudge you create, the less you want to start over and fix it. Maybe just keep it in mind for when/if the day ever comes that you decide to give up the struggle, assuming it remains your struggle.