Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 34
  1. #16
    kostakenny is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2022
    Posts
    12

    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.
    Attached Thumbnails Attached Thumbnails Comments Page.JPG  

  2. #17
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #18
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #19
    kostakenny is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2022
    Posts
    12
    I did not create this database, I inherited it. I'm just trying to accomplish this one task.

  5. #20
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #21
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    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.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  7. #22
    kostakenny is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2022
    Posts
    12
    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.
    Attached Files Attached Files

  8. #23
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #24
    kostakenny is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2022
    Posts
    12
    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.

  10. #25
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I already edited my previous post, probably while you were reading. Review again.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #26
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #27
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Okay, look at:
    Ooops, I forgot to apply the Seq sort. Fix that in the Sorting & Grouping design.
    Attached Files Attached Files
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #28
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861
    Quote Originally Posted by Micron View Post
    Methinks textbox height was WGM's idea so giving credit where it is due.
    .
    No, that credit goes to Minty.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  14. #29
    kostakenny is offline Novice
    Windows 10 Office 365
    Join Date
    Nov 2022
    Posts
    12
    Not sure how to apply the seq sort. Any chance you can walk me through it? Would be greatly appreciated.

  15. #30
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 2 of 3 FirstFirst 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Blank Space appearing on Subform
    By PaulaYoung in forum Forms
    Replies: 10
    Last Post: 04-27-2022, 06:18 PM
  2. Blank Space with Sub-Report on Main Report
    By StacyS in forum Reports
    Replies: 2
    Last Post: 10-23-2019, 09:02 PM
  3. Blank space between fields in report header
    By cebrower in forum Reports
    Replies: 3
    Last Post: 09-18-2018, 09:48 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums