Results 1 to 7 of 7
  1. #1
    jay is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    4

    Hide Fields With Null Values

    I need to prevent blank rows from displaying in a report. The blank rows represent fields with null values, which appear in almost every record.



    I have attached a simplified example with two columns. The left column contains labels and the right column contains text boxes. In this example, there are three blank rows. I would like to prevent these blank rows from displaying, and I would like the first three fields from the next record (not shown) to replace the blank rows.

    In the Details Section and in text box properties, I have the Can Grow and Can Shrink properties set to Yes. I have tried a variety of solutions but nothing has worked (probably because I am not correctly applying the solutions!). I found and joined this forum today (9-20-11) and would appreciate your help.

  2. #2
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Because the Lesson ID is there, it won't shrink. If you don't want nulls to be there just change the query that the report is based on to not include Nulls.

  3. #3
    jay is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    4

    Hide Fields With Null Values

    Thanks for the reply!

    I had tried adjusting the query before posting my question. This will show what a complete novice I am: I had entered Is Not Null as a criterion. Of course, this removes records that should display.

    I've been trying different criteria but nothing is working. Can you recommend a Criteria equation that would work? I would appreciate any further suggestions you may have. I have spent many hours trying to resolve this.

  4. #4
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Your example is a bit confusing. Can you post a screenshot of the report in design view, as well as the SQL of the query that the report uses as its record source?

  5. #5
    jay is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    4

    Hide Fields With Null Values

    I really appreciate your following up. I have attached three new screen shots: report in design view, query in design view, and sql. (The original post includes a screen shot of the report in layout view.) Please let me know if you need any further information. Thanks again.

  6. #6
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    Okay, the problem is your table structure is not normalized. You shouldn't have EO1, EO2, EO3, etc. in your table. You should have a table structure which makes those as rows of data:

    tblLensEOJunction
    LensID (FK from TOsTableExample)
    EOID (FK from a table like tblEO - see below)

    tblEO
    EOID - Autonumber (PK)
    EO - whatever datatype the data is

    Then you would be able to get the report to look like you want.

  7. #7
    jay is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2011
    Posts
    4

    Hide Fields With Null Values

    Ah, I see. Thank you! I'll make the changes you suggest. I can see that this will improve the database in several important ways. I won't be able to get to this right away, but I'm considering this solved. Thanks again for your help!

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

Similar Threads

  1. Importing numeric fields with null values from XML
    By hcorvallis in forum Import/Export Data
    Replies: 1
    Last Post: 10-20-2011, 08:09 AM
  2. Hide subreports when null
    By dssrun in forum Programming
    Replies: 5
    Last Post: 06-21-2011, 11:41 AM
  3. null values
    By ippy in forum Queries
    Replies: 3
    Last Post: 12-20-2010, 10:39 AM
  4. Combine two fields, Null fields involved
    By yes sir in forum Access
    Replies: 9
    Last Post: 10-03-2010, 09:20 AM
  5. Replies: 5
    Last Post: 03-20-2010, 08:30 AM

Tags for this Thread

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