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: 627
Thanks: 197
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,596
Thanks: 134
Thanked 1,513 Times in 1,485 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
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 6,552
Thanks: 88
Thanked 1,609 Times in 1,499 Posts
isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice isladogs is just really nice
Re: Remove Blank Address Lines

Or you can check for empty fields using the Nz function

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

Web links:
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.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Previously known as ridders

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.

Last edited by isladogs; 07-13-2018 at 03:14 AM.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs 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: 627
Thanks: 197
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: 655
Thanks: 0
Thanked 204 Times in 192 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
Suppressing blank address lines malcolmK Reports 1 12-15-2001 07:51 PM




All times are GMT -8. The time now is 04:53 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