Access Reports 1

[missyu]

Hi,

I am trying to show a list such as:

Groceries:
Apples
Oranges
Bananas

Where all the listed fruit is actually different variables (ex. Fruit1=true, Fruit2=true, Fruit3=true). I know this is not the best way to do this however, this is the way it is set up and I must find a way to report on it.

I've got it working however, is there anyway to eliminate the space if a variable is null? For example if Fruit1=true, Fruit2=false, and Fruit3=True - it is currently displaying:

Groceries
Apples

Bananas


I'm sure there's a way to do this but I can't seem to figure it out. Please help. Thanks!

 

[THoey]

Are these fields in a table or are they checkboxes on a form, and can we see the code you are currently using? It would make it easier for us to help if we now a bit more info... Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[missyu]

These are Fields in a table. There isn't any code to show you unfortunately. Basically, I inherited this database and they want a custom report.

So I just went in and used the report wizard and generated this report. There are 10 table fields where I want to display the field name if that field is set to True. I generated a query using IIF statements so that it would do this.

The report layout is as follows:

Groceries (HEADER)
(DETAIL)
-TableField1
-TableField2
-TableField3

If the TableField is = "True" (String), then the report will show the TableFieldName otherwise it will display null.

My problem is that it will not group the fields (that are NOT NULL) together...:
-TableField1 (="true&quot
-BlankSpace (because TableField2 was blank)
-TableField3 (="true&quot

When you have a series of TableFields in this list format, with uneven spacing, it is quite ugly. Therefore, I need the report to not show the ones that are null (even if they are just blank).

Hope that clarifies my problem. (Thanks in advance)

 

[MisterC]

set the "Can Shrink" property for the fields to "Yes"

 

[missyu]

Yes this has been done and it doesn't fix my problem. Thanks anyways

 

[MisterC]

hmmm...
works fine for me - no gaps.
What version of Access are you using?

 

[missyu]

I'm using Access 97. Isn't the "Can Grow" property only so that a field can expand if the contents are bigger than the set size? Then, the "Can Shrink" will shrink if the field is smaller, but it stays a BASE size doesn't it?

 

[MisterC]

No - Can Shrink will shrink it to nothing... Try it!

 

[petermeachem]

You are showing the fruit vertically yes?

Could you put a text box in and make the control source

= iif( ISNULL(Fruit1) , '' , [Fruit1] & Chr$(13) & Chr$(10) )

& iif( ISNULL(Fruit2) , '' , [Fruit2] & Chr$(13) & Chr$(10) )


Peter Meachem
peter@accuflight.com

 

[missyu]

petermeachem;

Thank you that worked great. Using the IIF as the control source did the trick!!

Thanks to everyone but a big kiss to you!
missyu

 

[petermeachem]

You are so kind. Anything else, just ask. Peter Meachem
peter@accuflight.com