splitting a field based to create a new field for every line after a carriage return

[Tonyvee73]

Hi All
My first post here, hope someone can help me?
I have a table named SL_ADDRESSES and a field in there named (AD_ADDRESS. This has 4 lines or so of data all with carriage returns at the end of each line. When i select the AD_ADDRESS field all data is in one field. Is there a way to separate this into separate fields based on lines of data before the carriage return? Really am stuck.
Thanks

 

[Andrzejek]

Split function is your friend:

Dim x As Integer
Dim ary() As String
...
ary = Split(rst!AD_ADDRESS.Value, vbNewLine)

For x = LBound(ary) To UBound(ary)
    Debug.Print ary(x)
Next x

---- Andy

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

 

[Tonyvee73]

Thanks Andy
How would i integrate this into a select fundtion?

 

[Andrzejek]

Could you clarify "select fundtion"?

---- Andy

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

 

[MakeItSo]

Hi Tony,

Sounds more like a SQL question to me; perhaps forum183 or forum436 would be a better suited forum? Depends on your DBMS.
In that case you also might want to rethink your DB structure. You should not hold multiple values in one field.

Hope this helps.
MakeItSo

"Knowledge is power. Information is liberating. Education is the premise of progress, in every society, in every family." (Kofi Annan)
Oppose SOPA, PIPA, ACTA; measures to curb freedom of information under whatever name whatsoever.

 

[Tonyvee73]

sorry, i mean how do i get into my SELECT query in sql?