Results 1 to 10 of 10
  1. #1
    DecaturLady is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2011
    Location
    Atlanta, GA
    Posts
    26

    Remove Field Labels where data is blank, null, etc.

    Friends,



    I’m creating a database, the object of which is to produce a Directory of the members of my high school class. Some, but not many, have two home addresses (ex. A snowbird with a Florida home and an NC mountain home). Where there is a second home, I’d like it to appear in the report.

    I’m at the design the report stage and much blank space is consumed where the classmate has no second home. I’d like to "tighten" the vertical layout by recovering the space for those entries where there is no second home. (And this is 95% of the Class.)

    How can I remove the labels for Home2? It has occurred to me that I could use an “if null” (or iif null) in the criteria fields of the query underlying the Report might work. However, I doubt that would element the labels (although it might).

    I’ve also considered the Label properties as a method for exclusion. (But doubt this is the solution.) Or is a "Second Home" sub-report the answer?

    PLUS, I don’t know how to construct the statement for the criteria fields.

    Has anyone suggestions? As ever, thank you in advance for all your help!

    DL

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    set the field invisible and the caption goes too.
    if caption is not connected to field box, just set the caption : lblCaption.visible = false

    Usu, if a record has > 1 subrecord:
    phones,
    houses,
    children....

    They are stored in a child table, then you can show only 1 or all.

  3. #3
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Simplest way, I think, would be to change the label to a textbox and set its source to =Address2:" & [Address2]. Set it to Can Shrink (Can Grow is No) and it should only appear if there is a value. Otherwise, I think you're looking at code in the OnFormat event.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    @Micron, the textbox will still show value from that expression. Using & to concatenate text with Null will display the text. Use + sign and result will be Null. Also missing quote mark.

    ="Address2: " + [Address2]
    Last edited by June7; 11-20-2017 at 03:05 PM.
    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
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    First was from habit. Second was typo. Thanks for catching those.

  6. #6
    DecaturLady is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2011
    Location
    Atlanta, GA
    Posts
    26

    How do I do it?

    Quote Originally Posted by Micron View Post
    Simplest way, I think, would be to change the label to a textbox and set its source to =Address2:" & [Address2]. Set it to Can Shrink (Can Grow is No) and it should only appear if there is a value. Otherwise, I think you're looking at code in the OnFormat event.
    I am a baby with plenty of theory and little practical application. How can I "change" the field label to a textbox? I suspect it is in the properties. I know that is where source, grow/shrink are located.

    PS: Don't know SQL. Am limited to Access interface.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    Do you already have a textbox bound to the Address2 field? Just delete its label and put the expression in textbox.

    "change" label by selecting the label then right click > Change To

    or just delete label and add a textbox to the report.
    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.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Thanks June7!
    Sorry for the confusion. I meant replace if you needed to keep the original control it was attached to. The expression will make the control a calculated one which if bound, will become updatable IIRC.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,820
    If you are going to use Access then you are going to learn SQL. Every query is an SQL statement, even when you build with the Query Designer. Switch to SQL view and you can see the SQL statement. This will help you learn SQL. Here is a good guide for SQL https://www.w3schools.com/sql/

    @Micron, a little confused by your statement. A control with a calculation cannot be BOUND and is not ever updatable.
    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.

  10. #10
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Foiled by my phone auto correct and a teeny tiny edit window again. I meant not updatable. Using laptop now. Thanks for clearing that up. As for the bound part, I see I wasn't real clear on that either. Was thinking of the current control probably being bound, which of course, it could not be.
    I would attempt to fix that post, but not sure I can, and it would tend to make your comment not applicable.

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

Similar Threads

  1. Remove Blank Space if Null
    By laniebe in forum Reports
    Replies: 3
    Last Post: 04-27-2017, 01:01 AM
  2. Centering Mailing Labels if line is Blank
    By shane201980 in forum Reports
    Replies: 4
    Last Post: 10-24-2012, 09:00 PM
  3. Getting rid of blank labels
    By eskybel in forum Reports
    Replies: 1
    Last Post: 06-28-2012, 07:52 AM
  4. Null field blank
    By brobb56 in forum Reports
    Replies: 3
    Last Post: 09-26-2011, 12:15 PM
  5. Changing field data into labels on the y-axis
    By slaterino in forum Access
    Replies: 1
    Last Post: 09-28-2010, 03:15 PM

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