Eliminating Empty Space

Question

Eliminating Empty Space

Tek

SSCommitted

Points: 1697
More actions
May 20, 2011 at 3:01 pm

#250511

Need a little assistance here guys. I'm creating a report that contains names and address that are stacked against each other, such as the below:
Business Name
John Doe
123 Street Name
City, State Zip
So you get the idea. However, there are some name and address fields that do not have any data. So when it generates the report, it leaves a gap between fields, for example:
Business Name
123 Street Name
City, State Zip
I'm wanting to eliminate the empty gaps. This is what I use:
=Fields!mailing_name1.Value + vbCRLF +
Fields!mailing_name2.Value + vbCRLF +
Fields!mailing_name3.Value + vbCRLF +
Fields!mailing_address1.Value + vbCRLF +
Fields!mailing_address2.Value + vbCRLF +
Fields!mailing_address3.Value
I understand using this will create the gaps I'm experiencing.
Can anyone assist one how to solve this?
Thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95461 More actions · Answer 1

I wouldn't normally post a looping method, but for the number of lines that you're dealing with I'm not sure that you'll get any faster.

DECLARE @crlf CHAR(2);

SET @crlf = CHAR(13) + CHAR(10);

DECLARE @YourString VARCHAR(500);

SET @YourString = '' + @crlf + '' + @crlf + '' + @crlf + 'Boston, MA 00125';

-- get rid of duplicating CRLFs

WHILE CHARINDEX(@crlf + @crlf, @YourString) > 0

SET @YourString = REPLACE(@YourString, @crlf + @crlf, @crlf);

-- get rid of leading CRLF

IF LEFT(@YourString,2) = @crlf

SET @YourString = SUBSTRING(@YourString, 3, 500);

SELECT @YourString;

From the VB side, you should be able to figure out the equivalent logic.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Niraj-627298 Hall of Fame Points: 3701 More actions · Answer 2

Hi,

Did you try using the textboxes inside a rectange control? I tried the same here on my box, and it seems like, if we keep the textboxes inside a rectangle control...and if any of the texbox appears to be 'NULL' or Empty, then there will not be any space issue...means the textbox having NULL data or Empty will not ask for any additional space...

Hope, this should help !!

Regards,

Niraj

Tek SSCommitted Points: 1697 More actions · Answer 3

Will this raise any of textboxes that are below the "empty" space?

Thanks!

Tek SSCommitted Points: 1697 More actions · Answer 4

To resolve the issue with blank spaces within the name and addresses, I created an expression with a multiple IIF expressions. I tested it and it works as I want it. However, I do have one small issue.

Within the IIF expressions, I have a field called Fields.Attention.Value. When the report is generated, the Attention field is displayed like this:

Attn: John Doe

Do get this, this is the expression I use:

IIF(Fields!Attention.Value > "", "Attn: " + Fields!Attention.Value, "")

My issue is this, when there's no value in the Attention field, I get a blank space between MailName3 and Address1. I get this because of the ELSE part of the expression. Is there something I can place in the ELSE part that will not generate a blank space.

Other than that, the rest of my many uses of IIF expressions works great.

Thanks!

Tek SSCommitted Points: 1697 More actions · Answer 5

After review the code, I got it working the way I wanted it.

Eliminating Empty Space

Cookies on SQLServerCentral