Line shift Char(13) in data

[Guest]

Hello

I have a database where the adresse field (text field) is build with Line
shift "Char(13)" for each section of the address. There are 3 lines in total
in the address field for each record

I would like to convert this text field in to 3 seperate fields - one for
each section. Is there a function in Access that can extract each of the
lines allowing me to get data in to new fields?

Thank you in advance for any help.

Best regards
Peter

 

[Brendan Reynolds]

Nothing built-in, but you could write one easily enough. For example
(assuming Access 2000 or later) ...

Public Function GetLine(ByVal Lines As String, _
ByVal Delim As String, ByVal LineNumber As Long) As String

Dim varWork
varWork = Split(Lines, Delim)
GetLine = varWork(LineNumber)

End Function

Here 'Lines' is the original data, 'Delim' is the character or characters
that delimit the lines, and line number is the (zero-based) number of the
line you want to return. For example ...

? getline("one,two,three",",",1)
two

.... or ...

? getline("one" & chr$(13) & "two",chr$(13),0)
one