Compressing blanks and suppressing blank fields on reports

  • Thread starter Thread starter Rick York
  • Start date Start date
R

Rick York

Hello all,

I posted this before on the wrong forum. I hope I get it
right this time.

First off, I'm pretty much a newbie in Access. I can do
some basic stuff, but I know little or nothing about
Visual Basic and creating macros.

Using the Wizards, I have created a report which basically
does everything I want except that it won't compress the
data or eliminate blank fields.

Some time ago I copied a routine from the knowledge base.
It looks like it would work. The problem is I have no
idea where to put it in the design view.

I'd appreciate any help any of you could give me.

Rick York
For the Sake of the Salmon
Portland, OR
 
Hello all,

I posted this before on the wrong forum. I hope I get it
right this time.

First off, I'm pretty much a newbie in Access. I can do
some basic stuff, but I know little or nothing about
Visual Basic and creating macros.

Using the Wizards, I have created a report which basically
does everything I want except that it won't compress the
data or eliminate blank fields.

Some time ago I copied a routine from the knowledge base.
It looks like it would work. The problem is I have no
idea where to put it in the design view.

I'd appreciate any help any of you could give me.

Rick York
For the Sake of the Salmon
Portland, OR
Click to expand...

Rick,

Since neither I, nor anyone else, could possibly any idea what routine
you copied (you didn't tell us), I doubt if anyone can tell you where
to place it.

If you wish to have data move up when there is no data in a field, the
normal method is to set the control's CanShrink property to Yes.

As long as there is no other control on that same line, the next
record's data will move up.
 
Fred,

You had a good point about not posting the routine. BTW,
I have all the fields set in the report to "can shrink".
They don't (shrink).

Here's the routine. It's only 16 lines,including
instructions, so I'll post the whole thing. It is
intended to be used for forms, but i would think the basic
idea should work for report output

1.Open the sample database Northwind.mdb.
2.Create a new form that is based on the Employees table,
and open it in Design view.
3.Add a text box control to the detail section of the
form, and then set the following properties.
4.Text Box
5.---------------------------------------------------------
------
6.ControlName: Full Address
7.ControlSource:
8.=IIf(IsNull([FirstName]),"",[FirstName] & " ") & _
9.IIf(IsNull([LastName]),"",[LastName]& Chr(13)& Chr(10))
& _
10.IIf(IsNull([ADDRESS]),"",[ADDRESS] & Chr(13) & Chr(10))
& _
11.IIf(IsNull([CITY]),"",[CITY] & ", ") & _
12.IIf(IsNull([REGION]) ,"",[REGION] & " ") & _
13.IIf(IsNull([PostalCode]),"",[PostalCode])
14.CanGrow: Yes
15.CanShrink: Yes
16.Open the form in Form view. Note that there are no
blank lines, even if some of the fields in the Employees
table are blank.

The problems are;
1) Where do I put it in the design of the report (or does
it belong there at all?
2) Is this suitable for what i want to do?

Thanks again for the attention.

Rick York
 
Fred,

You had a good point about not posting the routine. BTW,
I have all the fields set in the report to "can shrink".
They don't (shrink).

Here's the routine. It's only 16 lines,including
instructions, so I'll post the whole thing. It is
intended to be used for forms, but i would think the basic
idea should work for report output

1.Open the sample database Northwind.mdb.
2.Create a new form that is based on the Employees table,
and open it in Design view.
3.Add a text box control to the detail section of the
form, and then set the following properties.
4.Text Box
5.---------------------------------------------------------
------
6.ControlName: Full Address
7.ControlSource:
8.=IIf(IsNull([FirstName]),"",[FirstName] & " ") & _
9.IIf(IsNull([LastName]),"",[LastName]& Chr(13)& Chr(10))
& _
10.IIf(IsNull([ADDRESS]),"",[ADDRESS] & Chr(13) & Chr(10))
& _
11.IIf(IsNull([CITY]),"",[CITY] & ", ") & _
12.IIf(IsNull([REGION]) ,"",[REGION] & " ") & _
13.IIf(IsNull([PostalCode]),"",[PostalCode])
14.CanGrow: Yes
15.CanShrink: Yes
16.Open the form in Form view. Note that there are no
blank lines, even if some of the fields in the Employees
table are blank.

The problems are;
1) Where do I put it in the design of the report (or does
it belong there at all?
2) Is this suitable for what i want to do?

Thanks again for the attention.

Rick York
Rick,
Click to expand...
Click to expand...

Rick,
These 2 lines tell you where to place this expression:
6.ControlName: Full Address
7.ControlSource:
Click to expand...

In the Control source of an UNBOUND control,
named in this case, 'Full Address'.
*** It should be all on one line. Remove the line numbers and remove
the _ continuation characters. ***

=IIf(IsNull([FirstName]),"",[FirstName] & " ") &
IIf(IsNull([LastName]),"",[LastName]& Chr(13) & Chr(10))
& IIf(IsNull([ADDRESS]),"",[ADDRESS] & Chr(13) & Chr(10))
& IIf(IsNull([CITY]),"",[CITY] & ", ") & IIf(IsNull([REGION])
,"",[REGION] & " ") & IIf(IsNull([PostalCode]),"",[PostalCode])

Note also, In a Report, CanShrink will only work if there is no other
control on that same line.

CanShrink and CanGrow, in a Form, will ONLY work when the form is
printed (or previewed), not in Form view, so I have no idea why it was
even mentioned in your code snippet above, as forms are for data entry
and manipulation, not for printing.

Here is a simplified method of doing the above.
(You'll need to change the field names and expand upon it, as this is
only an example.)
Note the use of the () and + to concatenate the data, as well as the
&. If any part of the data within a set of () is missing, the entire
data within those () is treated as Null and not included in the
control.

=(FName] & (chr(13) + Chr(10) + [LName]) & (chr(13) +
Chr(10) + [address1]) & (chr(13) + Chr(10) + [address2]) &
[City] & ", " & [State] & " " & [Zip]

Adapt it as needed.
 
Back
Top