1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Access 2007: Using CanShrink and CanGrow to get rid of extra spaces

Discussion in 'Business Applications' started by ishatoo, Apr 22, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. ishatoo

    ishatoo Thread Starter

    Joined:
    Apr 9, 2013
    Messages:
    50
    Afternoon all,

    I am using Access 2007 and I am trying to get rid of extra spaces when there is no data to display on my report. I created a database that is used as a phone directory. I set the report to display two columns: one for the local information and the other for the corporate info. I have created unbound text boxes to display the info and an IF/ELSE statement if fields are blank. Since it is in two columns, when I expend the unbound text box on the corporate side to display all the info it also expends on the local side and gives me blank spaces. I have set the 'can shrink' and 'can grow' properties to 'Yes' on the property sheet but that didn't do anything.

    How can I remedy that?

    Thanks
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Can you show us a Screen Print?
    I think the Can grow/shrink only works in the Vertical direction, not on the width.
    Have you considered turning the report page to Landscape to give yourself some more width?
     
  3. ishatoo

    ishatoo Thread Starter

    Joined:
    Apr 9, 2013
    Messages:
    50
    yes I have but then I have to many pages. I am using the both property in the vertical layout and the final report need to be printed in portrait.

    Attached is a copy...as you will see all the information entered under the corporate section of dreamliner (right) is what is giving me the extra spacing on the other side (left). So I need to find a way to get rid of the spaces that are under 'Samantha Smith' and shift every thing up.
     

    Attached Files:

  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Have you made Details section height just one row?
    It should grow to accept the larger fields but stay small when there is no data.
     
  5. ishatoo

    ishatoo Thread Starter

    Joined:
    Apr 9, 2013
    Messages:
    50
    It won't let me do it as the unbound text box for the corporate information is large (report view.zip). And if I make that smaller then I loose data. See the attached files (report view 2.zip and report.doc).
     

    Attached Files:

  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    With that layout it probably isn't possible.
    It might be possible to use one field, but it would require a lot of formatting and might still not work.
     
  7. ishatoo

    ishatoo Thread Starter

    Joined:
    Apr 9, 2013
    Messages:
    50
    Would a 'trim function' work or is it only to remove extra spacing before and after a line of character
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    As far as I know it is as you say trim only removes spaces from the beginning and end of a line.
    If you can provide a copy of the database with some dummy data that will cause the problem I could have a look at it for you.
     
  9. ishatoo

    ishatoo Thread Starter

    Joined:
    Apr 9, 2013
    Messages:
    50
    Morning... I have attached the database. As you will see, I have a full adress entered in Dreamliner on the right and that is what causes the extra spacing on the left side.
     

    Attached Files:

  10. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    See the attached Excel sheet with a Print Screen of the Report, is it something like what you want?
     

    Attached Files:

  11. ishatoo

    ishatoo Thread Starter

    Joined:
    Apr 9, 2013
    Messages:
    50
    No. Your attachment looks exactly like what I currently have. What I am trying to do is to reduce the space that is below the first employee email address and the name of the second employee. Right now it is about a 2 inches gap. I need to have it at about a 1/4 of an inch.
     
  12. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    OK, I will try a few other things, but I don't hold out much hope.
    I will talk to you tomorrow, I am off out now.
     
  13. ishatoo

    ishatoo Thread Starter

    Joined:
    Apr 9, 2013
    Messages:
    50
    Ok. Thanks. Have fun. :)
     
  14. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Sorry, I don't think it can be done. The space is being allocated to the Invisible fields and I don't think that it can be turned off.
    As well as that as soon as you add the data to text71 it allocates the space for the Largest record's field and applies it to all of the records, overcoming the "Can Shrink" property.
    This is a bit odd as I am sure it varies the height for Bound Fields, if that is the case then it would be possible to build the data that you need in a "Print Table" using Memo type Fields and taking them directly to the report without the use of the concatenating VBA.
     
  15. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    OK, I have made some progress, the code that you are using is adding the vbCrLf whether there is any data in the fields or not, thus creating the spaces that the Report is forced to use, if you change the Concatenation VBA so that it tests for the data first and only adds the vbCrLf when there is data it does cut down on the amount of space allocated, but you still have the minor problem of moving all the Invisible fields up to ensure that they do not create extra space.
    The code I am using to replace your one line of code is this

    If Not IsNull(Me.[CyCorpNam]) Then
    Me.Text71 = Me.[CyCorpNam]
    End If
    If Not IsNull(Me.[CorpAdrSt]) Then
    Me.Text71 = Me.Text71 & vbCrLf & Me.[CorpAdrSt]
    End If
    If Not IsNull(Me.[CorpAdrSteNb]) Then
    Me.Text71 = Me.Text71 & vbCrLf & Me.[CorpAdrSteNb]
    End If
    If Not IsNull(Me.[CorpAdrCity]) Then
    Me.Text71 = Me.Text71 & vbCrLf & Me.[CorpAdrCity]
    End If
    If Not IsNull(Me.[CorpAdrStat]) Then
    Me.Text71 = Me.Text71 & vbCrLf & Me.[CorpAdrStat]
    End If
    If Not IsNull(Me.[CorpAdrZip]) Then
    Me.Text71 = Me.Text71 & vbCrLf & Me.[CorpAdrZip]
    End If
    If Not IsNull(Me.[CorpoFax]) Then
    Me.Text71 = Me.Text71 & vbCrLf & Me.[CorpoFax]
    End If
    If Not IsNull(Me.[CorpEmplFirNam]) Then
    Me.Text71 = Me.Text71 & vbCrLf & Me.[CorpEmplFirNam]
    End If
    If Not IsNull(Me.[CorpEmplLasNam]) Then
    Me.Text71 = Me.Text71 & vbCrLf & Me.[CorpEmplLasNam]
    End If
    If Not IsNull(Me.[CorpEmplTitl]) Then
    Me.Text71 = Me.Text71 & vbCrLf & Me.[CorpEmplTitl]
    End If
    If Not IsNull(Me.[CorpEmplPho]) Then
    Me.Text71 = Me.Text71 & vbCrLf & Me.[CorpEmplPho]
    End If
    If Not IsNull(Me.[CorpEmplEmail]) Then
    Me.Text71 = Me.Text71 & vbCrLf & Me.[CorpEmplEmail]
    End If
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1096825