Convert Single Line Address to Multi Line Address 2

[Moss100]

Hello,

I have a field in a database called [ADDRESS-SINGLE-LINE]

So for example the single field would contain: Flat 10, 24 Church Street, Boonbell, London, L4 5RT

I need to convert it to a multi line address which I could store in a single field [ADDRESS-MULTI-LINE}or perhaps generate it on the fly in a query.

So I need it to produce the following:

Flat 10
24 Church Street
Boonbell
London
L4 5RT

Thank you for any help.
Mark

 

[sxschech]

Would this be for display on a form or report? Will a comma always separate the data or could a comma exist within a particular part of the data?

 

[Moss100]

Hello - thank you for your interest.

Yes it would be for display on a form. The comma should always seperate.

Thank you Mark

 

[Andrzejek]

There is NO "need to convert it to a multi line address" in the data base
Just do this "for display on a form":

Dim strAdd As String

strAdd = "Flat 10, 24 Church Street, Boonbell, London, L4 5RT"

strAdd = Replace(strAdd, ", ", vbNewLine)
Debug.Print strAdd

You get:
[tt]
Flat 10
24 Church Street
Boonbell
London
L4 5RT
[/tt]
...unless the comma is not always followed by a space, i.e.[tt]
Flat 10, 24 Church Street,Boonbell, London,L4 5RT[/tt]
If you do have that situation in some of the fields, report back. You will need a little different logic.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[sxschech]

Expanding on Anydy's suggestion.
Create an unbound text box on your form
Create an Event Procedure in the On Current Event of the Form Detail
Edit the code accordingly similar to the field name and textbox name below

Private Sub Form_Current()
    Me.YourTextBox = Replace(Me.[ADDRESS-SINGLE-LINE], ",", vbCrLf)
End Sub

 

[Moss100]

Thank you.

I have ended up putting the code below directly in my query so the multi line address is always available.

LanMultiLine: Replace([Lan_Lease_Address],", ",Chr(13) & Chr(10))

It works fine, except where there is no data in the field LAN_LEASE_ADDRESS, it reports an error - but works fine when there is data.

Is there away to stop the word ERROR appearing?

Would I be better creating a public function and putting the function in the query?

Thank you very much - mark

 

[Andrzejek]

In the query replace NULL with an empty string using NZ function:
[tt]
Select ... NZ(Lan_Lease_Address, "") As Lan_Lease_Address, ...[/tt]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

sxschech,
With your Replace function with a comma without a Space after that, you will end up with a Space at the beginning of lines of text (except the first line):

[pre]
Flat 10
24 Church Street
Boonbell
London
L4 5RT[/pre]
:-(

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[sxschech]

Sorry and thanks for pointing out, didn't look too closely at the data when setting up the example.

 

[Moss100]

It's worked well thank you - i added the nz Mark