Eliminate Blank Rows

[Richard Yardley]

I have a mailing label report and would like to remove the space taken up by
rows with no info (move the other rows up).
I am a beginner using MS Access 2000 and would appreciate any help.

Thanks
Richard

[Mark]

You have a couple of options. If you mean that when you run a query, some
rows have no information, you can set the criteria of one of the fields to:
<>"" And Is Not Null

If you are basing the report on a table directly, you can use a Delete query
to remove the empty rows. Something like:
Delete * from Your_Table Where Your_Field is null;

If you set a primary key to your tables, you will not have the problem of
having entire rows with no information

Mark
"Richard Yardley" <rh*@iafrica.co m> wrote in message
news:r4******** ************@is .co.za...

I have a mailing label report and would like to remove the space taken up
by
rows with no info (move the other rows up).
I am a beginner using MS Access 2000 and would appreciate any help.

Thanks
Richard

[Richard Yardley]

Hi Mark,

Thanks for the response, but maybe I didn't make myself clear.
I am printing labels from a query. The query has fields amongst others,
"address1" and "address 2". In my label report, I have fields stacked
vertically with "address1" followed by "address2" followed by "Town". If the
field for "address 2" is empty for a particular record, I want the
placeholder for "address 2" to disappear in the report so that the "Town"
field follows directly under "address1" - i.e. eliminate the blank space
that I'm getting at the moment. I hope this makes sense!!

Regards,
Richard
--

Regards,
Richard

(H) 031 309 8133
(C) 082 851 1415

33 Jubilee Court
93 Clarence Road
Essenwood
Durban 4001
"Mark" <ma**********@n tlworld.com> wrote in message
news:cc******** ****@newsfe3-win.ntli.net...

You have a couple of options. If you mean that when you run a query, some
rows have no information, you can set the criteria of one of the fields to: <>"" And Is Not Null

If you are basing the report on a table directly, you can use a Delete query to remove the empty rows. Something like:
Delete * from Your_Table Where Your_Field is null;

If you set a primary key to your tables, you will not have the problem of
having entire rows with no information

Mark
"Richard Yardley" <rh*@iafrica.co m> wrote in message
news:r4******** ************@is .co.za...

I have a mailing label report and would like to remove the space taken up
by
rows with no info (move the other rows up).
I am a beginner using MS Access 2000 and would appreciate any help.

Thanks
Richard

[bruce]

On your label report, right click on the address2 field and go to
Properties. Click the Format tab, then set the 'Can Shrink' property
to 'Yes'. Hope this helps!

Bruce

[Richard Yardley]

Thanks for the suggestion Bruce. I tried the "CAN SHRINK" format option in
my report on a field and it worked fine when the field didn't contain data,
the lower fields moved up. Perfect, but some fields contain the following
expression for example:
=([StreetNumber] & " " & [StreetName])
and I guess because there is a space (" "), the field does not shrink.
I tried =TRIM([StreetNumber] & " " & [StreetName]) and =CLEAN([StreetNumber]
& " " & [StreetName]), but get an "Enter the parameter value" prompt.
Any suggestions??

--

Regards,
Richard

(H) 031 309 8133
(C) 082 851 1415

33 Jubilee Court
93 Clarence Road
Essenwood
Durban 4001
<br***@aristotl e.net> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .

On your label report, right click on the address2 field and go to
Properties. Click the Format tab, then set the 'Can Shrink' property
to 'Yes'. Hope this helps!

Bruce

[Martin Toombs]

I have dealt with similar issues by using an "if" statement.

Something like:

=iif([StreetNumber]is null,"",[StreetNumber]&" "&[StreetName])

that way if there is no street number (you could do the same thing with
StreetName of course) then it will go to the next line without putting
in the space.

Richard Yardley wrote:

Thanks for the suggestion Bruce. I tried the "CAN SHRINK" format option in
my report on a field and it worked fine when the field didn't contain data,
the lower fields moved up. Perfect, but some fields contain the following
expression for example:
=([StreetNumber] & " " & [StreetName])
and I guess because there is a space (" "), the field does not shrink.
I tried =TRIM([StreetNumber] & " " & [StreetName]) and =CLEAN([StreetNumber]
& " " & [StreetName]), but get an "Enter the parameter value" prompt.
Any suggestions??

--

Regards,
Richard

(H) 031 309 8133
(C) 082 851 1415

33 Jubilee Court
93 Clarence Road
Essenwood
Durban 4001
<br***@aristotl e.net> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.com.. .

On your label report, right click on the address2 field and go to
Properties. Click the Format tab, then set the 'Can Shrink' property
to 'Yes'. Hope this helps!

Bruce

[Darryl Kerkeslager]

"Richard Yardley" <rh*@iafrica.co m> wrote

Thanks for the suggestion Bruce. I tried the "CAN SHRINK" format option in
my report on a field and it worked fine when the field didn't contain data, the lower fields moved up. Perfect, but some fields contain the following
expression for example:
=([StreetNumber] & " " & [StreetName])
and I guess because there is a space (" "), the field does not shrink.
I tried =TRIM([StreetNumber] & " " & [StreetName]) and =CLEAN([StreetNumber] & " " & [StreetName]), but get an "Enter the parameter value" prompt.
Any suggestions??

Using + to concatenate the strings will result in a Null value rather than a
space when needed. For instance,

(StreetNumber + " ") & StreetName

will not have a leading space, since Null + " " = Null
After the parenthesis, the result of the Addition will be Null & StreetName,
which concatenates as you are used to.
Darryl Kerkeslager

[Richard Yardley]

Darryl, your solution worked perfectly - thank you

Regards,
Richard
Durban 4001

"Darryl Kerkeslager" <Ke*********@co mcast.net> wrote in message
news:B9******** ************@co mcast.com...

"Richard Yardley" <rh*@iafrica.co m> wrote

Thanks for the suggestion Bruce. I tried the "CAN SHRINK" format option in my report on a field and it worked fine when the field didn't contain data,

the lower fields moved up. Perfect, but some fields contain the following expression for example:
=([StreetNumber] & " " & [StreetName])
and I guess because there is a space (" "), the field does not shrink.
I tried =TRIM([StreetNumber] & " " & [StreetName]) and

=CLEAN([StreetNumber]

& " " & [StreetName]), but get an "Enter the parameter value" prompt.
Any suggestions??

Using + to concatenate the strings will result in a Null value rather than

a space when needed. For instance,

(StreetNumber + " ") & StreetName

will not have a leading space, since Null + " " = Null
After the parenthesis, the result of the Addition will be Null & StreetName, which concatenates as you are used to.
Darryl Kerkeslager