Results 1 to 12 of 12
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    Remove the blank rows in drtails

    I have tried Me.[Field Name].visible=True / False in multiple places in VBA. I have made the fields in that row Can Grow/Can Shrink = Yes. I have closed the details are of the report. I am trying to only show where there are comments in the details area of the report. I have googled etc.


    How do I do this? Report I am trying to fix = ReportThreadHeaders.





    Thanks
    Wayne
    Attached Files Attached Files

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,702
    Are you saying you don't want rows 2,3,4, etc to show up because there's no "comment" like "Simulation feeds through mission..." ?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Micron, Thay is correct. I only want to see th steps with comments.

  4. #4
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,702
    then try putting <>"" AND Is Not Null in the query comments field. If a report is bringing in too many records or records with no values in some field and you don't want that, then the problem almost always starts at the record source, not the report.

    You have 222 records where [comments] actually contains an empty string, and in your code, you check a lot for "" but never null. In all my years of stumbling through Access I've only ever come across one case where "" was actually needed. It's not obvious how you're getting them in there, and they're causing the query to retrieve those records. Then again I didn't look too hard.

    Not to be insulting, but you do know the difference between Null and "" ?

  5. #5
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    I need to proof read my stuff before posting. There are too many typos in here. I believe I have the hide rows when empty. Now I have duplicates of the thread headings where there are more than one comments.
    Attached Files Attached Files

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,702
    Go back to post 4 and look again. I bolded what you were supposed to put in the query. You only put in half of it.

  7. #7
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Micron,
    I did what you said in post 4, even adding the Is not Null to the <>"" makes no difference. I will get duplicates headers if there is more than one comment.

    Code:
    SELECT CheckList.EX, CheckList.ThreadNum, CheckList.ThreadDef, CheckList.ThreadStep, CheckList.ThreadStepDesc, DCount("*","[CheckList]","[ThreadNum]='" & [ThreadNum] & "'")+0 AS StepTTL, CheckList.VALEXPri, CheckList.ThreadStepNum, qryPassPercent.PerD, IIf([Pass]=True,DCount("Pass","Checklist","[ThreadNum]='" & [Threadnum] & "'"))+0 AS PrPass, CheckList.Capability, CheckList.Concept, CheckList.Demo, CheckList.Complete, CheckList.VALPRIThread, CheckList.InVALEX, CheckList.Comments, IIf(IsNull([Comments]) Or [Comments]="",Null,[ThreadStep]) AS TStep, CheckList.ID, CheckList.DateCOMPDone, CheckList.Pass, CheckList.Fail
    FROM CheckList LEFT JOIN qryPassPercent ON CheckList.ThreadNum = qryPassPercent.TNum
    WHERE (((CheckList.Comments)<>"" And (CheckList.Comments) Is Not Null)) OR (((CheckList.ThreadStepNum)=1))
    ORDER BY CheckList.EX, CheckList.VALEXPri, CheckList.ThreadStepNum;
    Thanks
    Wayne
    Attached Thumbnails Attached Thumbnails Duplicateheaders.PNG  
    Attached Files Attached Files

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,702
    Look again

    You still don't have what I wrote

    AND, not OR

  9. #9
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Micron,
    Perhaps I need to start another thread. The removal of blank fields you solved. I was referencing to where I have 2 comments under 1 header, the header repeats itself. I was wanting to stop that and keep all comments under 1 header.

    Apologies, I copy and pasted the wrong SGL. If you look in the above attached file it is AND, not OR.


    Thanks
    Wayne

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,702
    I made sure I read all your posts before replying. Can't recall anything that indicated the original problem was solved and that this was a new one.
    AND in your last posted db, all you have in that query field is <>""

    Take a bit more time to ensure you posts are a bit more accurate, OK?

    In the last db, you have 3 fields in a header section and you've changed the query quite a bit.
    Try moving your "Group On" to the top of the hierarchy.

    Click image for larger version. 

Name:	Thompyt2.jpg 
Views:	11 
Size:	21.0 KB 
ID:	37806

    Click image for larger version. 

Name:	Thompyt1.jpg 
Views:	12 
Size:	26.1 KB 
ID:	37807

  11. #11
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Thanks Micron, and thanks for the understanding. I have been doing so many tasks here in addition to this. I should know better as this thread does look like a cluster f... Again thanks! Such a simple fix on the grouping.

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,702
    URW.
    Glad we were able to sort it out!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 05-04-2018, 02:35 AM
  2. Remove Field Labels where data is blank, null, etc.
    By DecaturLady in forum Reports
    Replies: 9
    Last Post: 11-21-2017, 02:41 PM
  3. Remove Blank Space if Null
    By laniebe in forum Reports
    Replies: 3
    Last Post: 04-27-2017, 01:01 AM
  4. How to remove blank rows when I import from excel
    By timpepu in forum Import/Export Data
    Replies: 3
    Last Post: 09-14-2015, 04:04 PM
  5. Remove blank spaces after strings in fields
    By Modify_inc in forum Access
    Replies: 8
    Last Post: 08-18-2012, 06:30 PM

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