Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Endora is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2014
    Posts
    13

    Angry Suppressing records and minimizing row when there are null records

    I have been struggling with this for a long time with no results. I am trying to suppress null records on a report and at the same time minimizing the row if there are no records. Here is a copy of what I am trying to fix.

    I have changed Keep together=, can shrink=yes, can grow=yes


    This is what it looks like Click image for larger version. 

Name:	Picture5.jpg 
Views:	61 
Size:	26.0 KB 
ID:	15259and I want it to look like this. Click image for larger version. 

Name:	Picture6.jpg 
Views:	62 
Size:	31.4 KB 
ID:	15258

    Your help is greatly appreciated.
    Attached Thumbnails Attached Thumbnails Picture5.jpg  
    Last edited by Endora; 02-04-2014 at 07:17 PM. Reason: a newbee. making corrections

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Would a filter of Not Is Null accomplish what you want? The question is on what field?
    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. #3
    Endora is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2014
    Posts
    13
    The records are under the "Assistant Principal". I have tried "not null" for each, but I get no records. Do you see how there is a space on the last one between Juanita and Jaime?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Yes, I noticed that.

    The syntax in query is Not Is Null.

    If you put that criteria under that field, the upper records that don't have an assistant principal but do have principal and other data will not retrieve. Possibly you need several criteria.
    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.

  5. #5
    Xipooo's Avatar
    Xipooo is offline Sr. Database Developer
    Windows 8 Access 2013
    Join Date
    Jan 2014
    Location
    Arizona
    Posts
    332
    June7 is right. You need to compare the field to Not Is Null in your query. That will keep records with a null value for that field out of your results.

    It might also be possible (however somewhat rare) that you're data may not be null, it might be blank. In which case you would want to use <> "".

  6. #6
    amrut is offline Expert
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2012
    Location
    Dubai
    Posts
    614
    Apart from the suggestions above, I noticed that the height of controls used to display the fields is bit higher than required when looking at the data in first two rows.

  7. #7
    Endora is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2014
    Posts
    13
    Quote Originally Posted by amrut View Post
    Apart from the suggestions above, I noticed that the height of controls used to display the fields is bit higher than required when looking at the data in first two rows.
    I don't understand. This is so frustrating. is there a way that is can suppress blank information and mover the line up.

    Here is the design view.Attachment 15280

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The only way I know is to exclude the records.
    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. #9
    Endora is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2014
    Posts
    13
    Do you think that maybe I have the database set up all wrong. I have tried various ways and this is the only way that I can get somewhat of the results I need. I just have too much blank space.

  10. #10
    Endora is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2014
    Posts
    13
    Here is my query
    Click image for larger version. 

Name:	Picture19.jpg 
Views:	59 
Size:	72.6 KB 
ID:	15282

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    A lot of blank fields indicates a non-normalized data structure. I see allowance for 3 assistant principals. What if a school has 4 or 5? If most schools have less then fields are unused.

    All the assistant principals are in the same record. Seems the row height for each record must accommodate the longest content of any single text field and if wrapping with CanGrow is allowed, that will expand row height. Not sure it matters that you have arranged the fields vertically, still the same record.

    However, maybe I am wrong. Did you try the textbox CanShrink property setting?
    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.

  12. #12
    Endora is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2014
    Posts
    13
    I did change the Can Shrink to yes and the Can Shrink also to yes.

    the wide space between rows are the null for each of the record that is blank. If there is nothing, shouldn't it move up to avoid that wide blank space?

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    This seems to work, just tested.

    Set CanShrink and CanGrow to Yes.

    Set the textbox Height to 0.

    Scrunch the textboxes up tight against each other.

    Reduce height of the Detail section.
    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.

  14. #14
    Endora is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2014
    Posts
    13
    Quote Originally Posted by June7 View Post
    This seems to work, just tested.

    Set CanShrink and CanGrow to Yes.

    Set the textbox Height to 0.

    Scrunch the textboxes up tight against each other.

    Reduce height of the Detail section.
    I did what you said, but this is what it looks like:Click image for larger version. 

Name:	example1.jpg 
Views:	57 
Size:	82.9 KB 
ID:	15289

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Since the solution worked for me and I can't replicate your results, if you want to 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.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. records w/null
    By slimjen in forum Queries
    Replies: 6
    Last Post: 07-03-2013, 09:39 AM
  2. Replies: 8
    Last Post: 08-13-2012, 04:30 AM
  3. Suppressing records in a report
    By snowboarder234 in forum Reports
    Replies: 1
    Last Post: 07-11-2012, 07:52 PM
  4. Do not show null records
    By brobb56 in forum Forms
    Replies: 1
    Last Post: 09-23-2011, 02:29 PM
  5. Not showing records with null sum
    By eww in forum Queries
    Replies: 3
    Last Post: 04-04-2011, 03:10 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