Solved

Can Shrink using columns

Posted on 2011-09-27
9
338 Views
Last Modified: 2012-05-12
I'm trying to suppress blank fields in the detail of a report that has two columns of data.  See attached file for further explanation.  
EEReportProblem.doc
0
Comment
Question by:KarenPRuskin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 85
ID: 36713636
Are those all bound textboxes? In other words, is SS11 a bound textbox, and SS12 a bound textbox, and so on? If so, then I don't see how you'll be able to do this, but then we'd need to know more about your table design. Can you show us the tables you're using as a base for this report?
0
 

Author Comment

by:KarenPRuskin
ID: 36713668
In the query that the report uses, I've got 34fields based on whether the underlying table field is Yes or No.  eg: SS11: IIf([SITE SS11]=Yes,"SS 1.1     SPRINKLER","") where SITE SS11 is the table field.  
0
 
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 250 total points
ID: 36713676
Then it would seem that you have an improper design, but that is hard to say without seeing your tables. Could you upload a copy of your database? We don't really need to see any data, just the table structure.
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 26

Accepted Solution

by:
Nick67 earned 250 total points
ID: 36713721
What you want to do there is hard, given what I suspect your table structure is.
There are many, many fields, and one detail section.
CanShrink works on the section in general, and on each control.
What it doesn't do is move any controls whatsoever.
CanGrow moves stuff, forcing it down the page.
CanShrink undoes CanGrow, but won't dynamically re-arrange stuff

For that you must either code, or change your data structure.
In changing your data structure, you'd have an 'InspectionID' and an ItemPresentID
You'd then have one record for each item present in an inspection, where now you have one inspection record and many fields
You wouldn't have the blanks entered, and wouldn't need to suppress them.

Here's a sample to demonstrate the difference
Different-Structures.mdb
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36713734
@LSMConsulting
Lots of folks with denormalized data today!
http://www.experts-exchange.com/Microsoft/Development/Q_27327999.html
Same kind of deal.
0
 

Author Closing Comment

by:KarenPRuskin
ID: 36713784
This database was presented to me to modify the existing reports.  I agree with both of you, bad bad design and may spend some time redesigning the table layout, as suggested.   It's always more than a simple 'report modification' ! !  Anyway, keeps me in a job.  Thanks for your fast response.  BTW, I'm in New Zealand.  Go The All Blacks.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36713805
Watched the Canucks tie the Japanese last night.
They'll get killed by the All Blacks, of course, but Kleeburger's been fun to watch.
That beard!
Hopefully they finish third in the pool and can have another go around in 4 years.
Incidentally, the local boys (Grande Prairie Alberta Pop. 52,000) won the Edmonton Rugby Union E3 title over the Edmonton (Pop. 1 Miilion) squad on the weekend.
Next weekend they play the Calgary (also Pop. 1 million) squad for the provincial championship.
Good stuff.
Go Canada!
0
 

Author Comment

by:KarenPRuskin
ID: 36713872
Wow, what a small world.  I'm originally from Edmonton so know the places you mention.  My husband (a New Zealander) and I now run a guest house in Whangarei, New Zealand and both the Canadian and Japanese teams managers/coaches stayed with us during their games here (Keiran Crowley, John Kirwan, etc).  Nothing like name dropping!  Good to sing Oh Canada once again with a fanatic crowd and amazing that they got a team together - doesn't resemble ice hockey in the slightest!  Personally, I think rugby is absolutely rugged, no padding anywhere.   Nice to meet a fellow fan in such an unlikely space as EE.  Cheers Nick, Regards Karen.  
0
 
LVL 26

Expert Comment

by:Nick67
ID: 36717803
It's a small world indeed.
Glad to have helped
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question