How to shrink and compact a report? Please help 1

[suddu]

Hi, Thanks everybody on this forum. I have used it off and on and has helped me a lot. But now I am stuck with a problem, and I am not able to solve it. To all the Tech Guru's out there, any help will be greatly appreciated.

I have a hospital form, which has many checkboxes, now a patient may have only few of those checked and not the rest. I want the report to print only those checked but next to each other without much space between them.
With the help from this forum I have been able to figure out how to show if they are checked or not, but now the issue is compacting the report to only those that are checked or textboxes that have text in them.

Another issue is to show the label of the textboxes only if they have any data in it, otherwise hide the textbox and shrink the space between them and have the next textbox wth the data in it takes it place.

Thanks in advance to everybody here.

 

[JoeEskimo]

Assuming your form is capturing data and sending it to a table,

1. Design a new query that has criteria set for only checked boxes for that person on the form.

2. Design a report to print your new query

3. Add a button to your form that prints the report

I hope this helps.

 

[dhookom]

You can set the Can Shrink property of text boxes but this only shrinks if the values are null. This suggests you want to convert your Yes/No fields to "x" for Yes or Null for No. For instance, if you have yes/no fields for colors you could modify your query to create fields like:
IIf(Red,"x",Null) AS IsRed, IIf(Green,"x",Null) AS IsGreen, IIf(Yellow,"x",Null) AS IsYellow,...
Then in your report, use text boxes in place of the label and the check box. The check boxes would be text boxes with control sources like: IsRed, IsGreen, IsYellow,...
Set them to Can Shrink: Yes and set their font to WingDing.

Labels will not shrink but text boxes can. This means change your labels to text boxes and set their control sources to:
="Red "+[IsRed]
="Green "+[IsGreen]
="Yellow "+[IsYellow]
etc.
Again set these so they can shrink. If there are no controls to the left or right of these then the controls will shrink.

BTW: your table structure sounds un-normalized. For instance the colors should be individual records in a related table.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[suddu]

Thanks a lot Joe for your quick reply. Could you tell me what criteria to set up in the query spo I can try and see if that will help

 

[dhookom]

suddu,
I think Joe and I have totally different ideas of what you want displayed. My assumption is that you want something like:
X Red
Green
X Yellow
Brown
X Blue

Printed like:
X Red
X Yellow
X Blue

I think if you don't want to get into a lot of messy code, you can use the method that I suggested.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[suddu]

Hi Duane,
Thanks to you too for the reply. Well What you said in your second post is exactly right and thats what i am looking at. regarding the table being normalized or not , I donot know if I have a problem here. All my data is coming from one table and this has one form aatched to it , with lots of checboxes and few combo and list boxes as well as textboxes. y form works great and so do rest of the things it's just the report that I have problem with. I was trying to achieve the same result as u described but the shrink property works to a certain extent and leaves the space between it, also the label is a problem.
If you could tell me in detail how to accomplish the task, would be greatly helpful. I mean how to change the query criteria, I know wher to put it but what code exactly and all.
Thanks

 

[dhookom]

Joe mentioned query criteria, not me. I wouldn't do anything with criteria at this point.
What do you mean by "the label is a problem"?
Also, do you understand my earlier statement "If there are no controls to the left or right of these then the controls will shrink"?
Does your report record source display "X" or null in all of your yes/no columns? If not, go back and re-read my earlier reply.

What is your SQL of your report's record source?

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[suddu]

Huuuuu...........

Well, sorry for mixing the two. My record source puts a -1 for yes, i think thats standard for access & I know that I can substitute those in plce of x , but what i meant by label is that I want to show on the report the checkbox and the corresponding label very close or abut to each other , and only those that were checked on the form. I made the report using wizard from the access databse and used the table a smy record source, if thats what you are asking about.
Thanks

 

[dhookom]

If you re-read my first response, I suggested that you can't shrink a control unless it can be null. That is why I asked you to change your query to substitute the expression:
IsRed: IIf(Red,"x",Null)
where Red corresponds to one of your yesno fields. Every one of your yesno fields that you want to shrink must be replaced by this type of expression.

If you don't understand this, reply back with several names of your yesno fields that you want to shrink.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[suddu]

While trying to do as you said, the query returns an error for "AS". Also the few checkboxes are :

X CHF X pulmonary edema X Hypertension
X PE X Heart Failure X Afib

and each X reprsents a checkbox, now say i have only chf and afib checked i want to show it up as

X CHF X Afib


and want any other checkbox below it like date toshow it up with the date label below that without much space
Thanks

 

[dhookom]

Consider your report much like an excel spread sheet. Apparently you have several controls left to right in the same row. In Excel, you can't shrink the cell vertically in a row and not shrink all the other cells in the same row. This holds true in an Access report also. If you have CHF to the left of Pulmonary Edema to the left of Hypertension, none of these will shrink to save vertical space unless they are ALL null.

This is what I meant in two of my posts by "If there are no controls to the left or right of these then the controls will shrink".

The normalization comments I made earlier had to do with storing data values as field names. Your field names CHF, PE, ... are actually data values. A key indicator of this is that you would need to add another field if you want to also track hemorrhoids. If these are symptoms or conditions, it might be better to create a related table that adds one record per condition.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[suddu]

Thanks Duane,
I will try that out, I understand that it won't shrink if more than one on the same line, unless null. but the other problem is like if I have a textbox on the form with a certain value in it I want the text box with the value to appear in it and the label too with what its for otherwise hide the text and the label both. I have been able to hide the textbox if ther is novalue but the label appears irrespective, How do I get rid of that.
And the error that I talked about in my previous post, as u suggested, if I put the expression in the query for IIf(Red,"x",Null) AS IsRed, it gives me an error on the expression not right with "As" highlighted.

Your help greatly appreciated

 

[dhookom]

In the middle of my first response, I provided a solution of how to make the "label" dissappear if the value of the field is null.

The IIf(Red,...) is done in the query by creating a calculated column:
IsRed: IIf(Red,"x",Null)
of course you would need to substitute your own yesno field names.
IsCHF: IIf(CHF,"x",Null)
IsAfib: IIf(Afib,"x",Null)

Duane
MS Access MVP
Find out how to get great answers faq219-2884.