Solved

Can Shrink using columns

Posted on 2011-09-27
9
334 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
  • 4
  • 3
  • 2
9 Comments
 
LVL 84
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 84

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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

773 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