Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-13-2018, 01:34 AM   #1
ECEK
Newly Registered User
 
Join Date: Dec 2012
Posts: 581
Thanks: 185
Thanked 4 Times in 4 Posts
ECEK is on a distinguished road
Remove Blank Address Lines

I'm struggling to concatenate the data where two or more lines are blank.

I have an unbound text box on my form with the following.

=[Address_Line_1]+Chr(13) & Chr(10)+[Address_Line_2]+Chr(13) & Chr(10)+[Address_Line_3]+Chr(13) & Chr(10)+[Address_Line_4]+Chr(13) & Chr(10)+[City_Town]+Chr(13) & Chr(10)+[County]+Chr(13) & Chr(10)+[Country]+Chr(13) & Chr(10)+[Postcode]

However if Address_Line_3 AND Address_Line_4 are both blank then I get the following result:

1 Any Street
AnyArea

Townsville
Countyshire
United Kingdom
123 ABC

as you can see there is a blank on line three.

This anomaly occurs when there are two or more consecutive blank address lines.

Can anybody suggest a solution?

ECEK is offline   Reply With Quote
Old 07-13-2018, 01:43 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,348
Thanks: 126
Thanked 1,460 Times in 1,432 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: Remove Blank Address Lines

You can use a function to do this ;
Code:
Public Function CanShrinkLines(ParamArray arrLines())
    ' Pass this function the lines to be combined
    ' For example: strAddress =
    ' CanShrinkLines(Name, Address1, Address2, City, St, Zip)
    Dim X     As Integer, strLine As String
    For X = 0 To UBound(arrLines)
        If Not IsNull(arrLines(X)) And arrLines(X) <> "" Then
            strLine = strLine & arrLines(X) & vbCrLf
        End If
    Next
    CanShrinkLines = strLine
End Function
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
ECEK (07-13-2018)
Old 07-13-2018, 01:50 AM   #3
ridders
Newbee moderator
Gold Supporter
 
ridders's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 5,532
Thanks: 79
Thanked 1,368 Times in 1,277 Posts
ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough ridders is a jewel in the rough
Re: Remove Blank Address Lines

Or you can check for empty fields using the Nz function

__________________
Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left and leave a comment.

New example databases:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


-------------------------
nil illegitimi carborundum est

Last edited by ridders; 07-13-2018 at 03:14 AM.
ridders is offline   Reply With Quote
The Following User Says Thank You to ridders For This Useful Post:
ECEK (07-13-2018)
Old 07-13-2018, 02:05 AM   #4
ECEK
Newly Registered User
 
Join Date: Dec 2012
Posts: 581
Thanks: 185
Thanked 4 Times in 4 Posts
ECEK is on a distinguished road
Re: Remove Blank Address Lines

Thanks you guys
ECEK is offline   Reply With Quote
Old 07-13-2018, 03:08 AM   #5
moke123
Me.Dirty=True
 
moke123's Avatar
 
Join Date: Jan 2013
Location: Massachusetts
Posts: 609
Thanks: 0
Thanked 192 Times in 181 Posts
moke123 is on a distinguished road
Re: Remove Blank Address Lines

You may also be able to use the Law of Propagating nulls.

Code:
Public Function LOPN(strIN As Variant) As Variant

LOPN = (strIN + vbNewLine)

End Function
then wrap your string
Code:
LOPN([Address_Line_1]) & LOPN([Address_Line_2]) & LOPN([Address_Line_3]) & LOPN([Address_Line_4]) & LOPN([City_Town]) & LOPN([County])  & LOPN([Country]) & LOPN([Postcode])

moke123 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] export to text: remove blank lines/empty rows anski Modules & VBA 6 05-26-2018 08:49 PM
Report contains address with blank lines Mike Hughes Reports 25 02-04-2011 04:58 AM
Blank address lines in mailmerge Malcy General 2 12-30-2007 11:38 PM
Avoiding blank Address lines in reports mazza Reports 17 11-22-2007 09:49 AM
[SOLVED] Suppressing blank address lines malcolmK Reports 1 12-15-2001 07:51 PM




All times are GMT -8. The time now is 04:05 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World