Replace carriage returns in a string with comma

[Palmyra]

One of the fields in a table I've been given has multiple values separated by carriage returns. Can I used vb Replace to change those carriage returns to commas?

Thanks.

 

[TonyJollans]

Yes, of course, you can use Replace in VBA - if you have the data in VBA but ...

... if what you're really asking is can you use Replace in SQL in Access then the answer is No - or not directly, so ...

... can you give a few more details of what you want to know?

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[Palmyra]

The field has unreadable characters separating values. The unreadable character is an ascii 2 (or double quote).
I know the third character is always an ascii 2.

I open a recordset in vba. I want to replace the ascii2 with a pipe |

strSQL = "Select tsindex, tlindex, tbatch, trecbuffer from ewin_tlabel"
Set rs = DB.OpenResults(strSQL)

Dim txt As String
Dim fld As Integer

txt = rs.Value("trecbuffer")
fld = Asc(Mid$(txt, 3, 1))
If InStr(rs.Value("trecbuffer"), fld) > 0 Then
txt = Replace(txt, fld, "|")

End If

My syntax is off or something, because although fld does return the ascii2, my If..isn't returning anything.

Thanks.

 

[PHV]

You may try something like this:
txt = Replace(rs.Fields("trecbuffer"), Chr(2), "|")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Palmyra]

Thanks. I tried below and got 'Error 438, Object doesn't support this property or method'

strSQL = "Select tsindex, tlindex, tbatch, trecbuffer from ewin_tlabel " & _
"order by tsindex asc"

Set rs = DB.OpenResults(strSQL)

Dim txt As String
Dim fld As Integer

txt = rs.Value("trecbuffer")

txt = Replace(rs.Fields("trecbuffer"), Chr(2), "|")
rs.MoveNext

End Sub

 

[PHV]

Sorry for the typos:
Set rs = DB.OpenResults(strSQL)
Dim txt As String
txt = Replace(rs.Value("trecbuffer"), Chr(2), "|")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Palmyra]

Run-time error 94 - 'Invalid use of null'.

I know txt is populated.

 

[PHV]

I know txt is populated
How do you know that ?
Furthermore, what do you want if the SQL returns many rows ?

BTW, what is DB ? (I don't know which object exposes an OpenResults method returning a Recordset ...)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886