Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

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


(!)

ishatoo's Avatar
ishatoo   (Aicha) ishatoo is offline
Member with 50 posts.
THREAD STARTER
 
Join Date: Apr 2013
Location: Laurel, MD
Experience: Intermediate
22-Apr-2013, 12:30 PM #1
Solved: Access 2007: Using CanShrink and CanGrow to get rid of extra spaces
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
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,509 posts.
 
Join Date: Mar 2005
Location: UK
22-Apr-2013, 01:10 PM #2
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?
ishatoo's Avatar
ishatoo   (Aicha) ishatoo is offline
Member with 50 posts.
THREAD STARTER
 
Join Date: Apr 2013
Location: Laurel, MD
Experience: Intermediate
22-Apr-2013, 01:52 PM #3
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
File Type: zip Report extra space.zip (193.4 KB, 3 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,509 posts.
 
Join Date: Mar 2005
Location: UK
22-Apr-2013, 02:09 PM #4
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.
ishatoo's Avatar
ishatoo   (Aicha) ishatoo is offline
Member with 50 posts.
THREAD STARTER
 
Join Date: Apr 2013
Location: Laurel, MD
Experience: Intermediate
22-Apr-2013, 03:11 PM #5
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
File Type: docx Report View.docx (196.7 KB, 23 views)
File Type: zip report.zip (196.5 KB, 9 views)
File Type: zip Report View 2.zip (189.9 KB, 7 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,509 posts.
 
Join Date: Mar 2005
Location: UK
22-Apr-2013, 03:39 PM #6
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.
ishatoo's Avatar
ishatoo   (Aicha) ishatoo is offline
Member with 50 posts.
THREAD STARTER
 
Join Date: Apr 2013
Location: Laurel, MD
Experience: Intermediate
22-Apr-2013, 03:42 PM #7
Would a 'trim function' work or is it only to remove extra spacing before and after a line of character
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,509 posts.
 
Join Date: Mar 2005
Location: UK
23-Apr-2013, 05:09 AM #8
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.
ishatoo's Avatar
ishatoo   (Aicha) ishatoo is offline
Member with 50 posts.
THREAD STARTER
 
Join Date: Apr 2013
Location: Laurel, MD
Experience: Intermediate
23-Apr-2013, 08:20 AM #9
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
File Type: zip Directory.zip (428.2 KB, 12 views)
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,509 posts.
 
Join Date: Mar 2005
Location: UK
23-Apr-2013, 01:23 PM #10
See the attached Excel sheet with a Print Screen of the Report, is it something like what you want?
Attached Files
File Type: xlsx Can Grow.xlsx (155.3 KB, 21 views)
ishatoo's Avatar
ishatoo   (Aicha) ishatoo is offline
Member with 50 posts.
THREAD STARTER
 
Join Date: Apr 2013
Location: Laurel, MD
Experience: Intermediate
23-Apr-2013, 01:56 PM #11
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.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,509 posts.
 
Join Date: Mar 2005
Location: UK
23-Apr-2013, 02:06 PM #12
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.
ishatoo's Avatar
ishatoo   (Aicha) ishatoo is offline
Member with 50 posts.
THREAD STARTER
 
Join Date: Apr 2013
Location: Laurel, MD
Experience: Intermediate
23-Apr-2013, 02:11 PM #13
Ok. Thanks. Have fun.
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,509 posts.
 
Join Date: Mar 2005
Location: UK
24-Apr-2013, 05:38 AM #14
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.
__________________
OBP
I do not give up easily
OBP's Avatar
OBP OBP is offline OBP is a Trusted Advisor with special permissions.
Computer Specs
Trusted Advisor with 18,509 posts.
 
Join Date: Mar 2005
Location: UK
24-Apr-2013, 05:55 AM #15
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
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑