Line Break in text box on report not showing

[ben1234zz]

Hi

We have an ADP project linked to SQL Server.

A view on the server returns an address field with CHAR(13) line breaks, however these show in the text box on the report as boxes without breaks.

I can amend the SQL Server view to suit, ive tried CHAR(10) and CHAR(9)with no luck

Thanks
B

 

[lameid]

Access wants a Carriage Return AND a line break...

CHAR(10) & Char(13)

If you really want a blank line, you need 2 sets (use the above for each enter you would press in word).

In VBA code you can use the built in constant instead, vbCRLF

 

[ben1234zz]

Hi

Thanks for your post.

I have amended the view for CHAR(10) + CHAR(13) but still no luck, it now looks like:

SELECT AddressID, CustomerID, CASE WHEN Company_Name IS NULL OR
LTRIM(RTRIM(Company_Name)) = '' THEN '' ELSE Company_Name + CHAR(10) + CHAR(13) END + CASE WHEN Contact_Name IS NULL OR
LTRIM(RTRIM(Contact_Name)) = '' THEN '' ELSE Contact_Name + CHAR(10) + CHAR(13) END + CASE WHEN Address_1 IS NULL OR
LTRIM(RTRIM(Address_1)) = '' THEN '' ELSE Address_1 + CHAR(10) + CHAR(13) END + CASE WHEN Address_2 IS NULL OR
LTRIM(RTRIM(Address_2)) = '' THEN '' ELSE Address_2 + CHAR(10) + CHAR(13) END + CASE WHEN Address_3 IS NULL OR
LTRIM(RTRIM(Address_3)) = '' THEN '' ELSE Address_3 + CHAR(10) + CHAR(13) END + CASE WHEN Address_Town IS NULL OR
LTRIM(RTRIM(Address_Town)) = '' THEN '' ELSE Address_Town + CHAR(10) + CHAR(13) END + CASE WHEN Address_County IS NULL OR
LTRIM(RTRIM(Address_County)) = '' THEN '' ELSE Address_County + CHAR(10) + CHAR(13) END + CASE WHEN Address_Postcode IS NULL OR
LTRIM(RTRIM(Address_Postcode)) = '' THEN '' ELSE Address_Postcode + CHAR(10) + CHAR(13) END AS Address
FROM dbo.SM_Addresses

Thanks
B

 

[lameid]

Can you post some sample data and the desired result? On the surface, it looks good to me.

 

[ben1234zz]

Sample data would be:
Company_Name: My Company
Contact_Name: My Contact
Address_1: First Line of Address
...

The aim of the view is to create an address for a letter. But omitting missing lines for example not all addresses will have Address_3 but i dont want a blank line, e.g.:

My Company
My Contact
Address 1
Address 2

Town
Postcode

However if there is a better way of achiving this in the report (without the view) i will be happy.

Thanks
B

 

[lameid]

You could use similar code on the fields in the control source of the textbox on your report, just use an amperstand (&) instead of the plus sign (+).

You could also put controls on your report stacked and set each one's Can Shrink Property to yes and the same for the section they are in. So long as there is no control that is at the same level as the address controls vertically, it should shrink up for blank values nicely.

 

[ben1234zz]

Hi

Thanks for your post,

Regarding your first suggestion, how could I use the CASE statement in the control source, I cannot seem to get it to work?

Alternatively for your second suggestion, can I add multiple details sections to a report (I cannot seem to be able to do this)

Thanks
B

 

[lameid]

I would probalby take a different approach... NZ is equivalent to the SQL isnull function (isnull function in access returns if the single parameter is null). Access also supports Trim so you don't need Ltrim and Rtrim but they exist too.
I've started your control source below. In Reports you have to use an equal sign in the control source if you are not using a single field. Note that I am using the trim function nested to get rid of extraneous CR/LF white space (see red parenthesis).

=Trim[red]([/red]Trim(NZ(Company_Name, "")) & chr(10) & Chr(13) & Trim(NZ(Contact_Name, ""))[red])[/red] & chr(10) & Chr(13)

In your use of case, you would use the IIF function in Access. For more complex use, you would have to write a vba function using select case and pass values to it from your query.

 

[ben1234zz]

Hi

Thanks for your post

There must be something wrong with Access / My Report, as its still displaying the boxes instead of the line breaks.

Is it because its an ADP maybe?

Thanks
B

 

[lameid]

Is your textbox tall enough or is the can grow property set to true?

 

[ben1234zz]

Yes to both, its taller than required and can grow is true.

 

[lameid]

While I have determined ADP's are Evil in my opinion, I believe it should simply work.

If you link your view into an MDB file as a table and use it as a recordsoource, does it work?