Replacing ASCII characters in a text string

[Paul Abbey]

Can anyone direct me on how to replace the line break
character with a tab character in a given field. That is
Chr(13) with Chr(9)?

 

[Dirk Goldgar]

Can anyone direct me on how to replace the line break
character with a tab character in a given field. That is
Chr(13) with Chr(9)?

You can do it easily enough, but it may not give you the result you
want. When the field is displayed in a datasheet or form, you won't see
the text nicely spaced. Instead, you'll see a little box that
represents the non-displayable character.

Here's example SQL of an update query to replace the Chr(13)+Chr(10)
combination (carriage return and line-feed) with Chr(9):

UPDATE MyTable
SET MyField = Replace(MyField, Chr(13) & Chr(10), Chr(9));

Access uses the CR/LF combination, not a CR alone, as a line break. If
you're dealing with data that really has just the CR, take out the "&
Chr(10)".

 

[Paul Abbey]

Thank you Dirk for your message.

On trying your suggestion, I got an error 3085 message
that read "Undefined function 'Replace' in expression"
both when I tried to create an UPDATE query and when I did
it by writing the SQL.

Paul Abbey

 

[Dirk Goldgar]

Thank you Dirk for your message.

On trying your suggestion, I got an error 3085 message
that read "Undefined function 'Replace' in expression"
both when I tried to create an UPDATE query and when I did
it by writing the SQL.

Paul Abbey

What version of Access are you using? Access 97 had no Replace()
function, but I can give you a substitute for it. The Replace()
function was introduced with Access 2000, but the original release of
that wouldn't let you use it in a query, so you have to write a
"wrapper" function for it.

 

[Paul Abbey]

I am using Access 2000, which probably explains the
problem. I would be very grateful if you could forward the
wrapper, if you have it.

Paul

 

[Dirk Goldgar]

I am using Access 2000, which probably explains the
problem. I would be very grateful if you could forward the
wrapper, if you have it.

Paste this function into a standard module, and call "fReplace" in your
query instead of "Replace":

'----- start of code -----
Function fReplace( _
Expression As String, _
Find As String, _
Replace As String, _
Optional Start As Long = 1, _
Optional Count As Long = -1, _
Optional Compare As Integer = vbDatabaseCompare)

fReplace = VBA.Replace( _
Expression, Find, Replace, Start, Count, Compare)

End Function
'----- end of code -----

An alternative might be to apply the latest Access 2000 service pack,
which I *think* will allow you to call the Replace function directly
from your query.

 

[Paul Abbey]

Thanks a lot Dirk

Paul

-----Alkuperäinen viesti-----


Paste this function into a standard module, and call "fReplace" in your
query instead of "Replace":

'----- start of code -----
Function fReplace( _
Expression As String, _
Find As String, _
Replace As String, _
Optional Start As Long = 1, _
Optional Count As Long = -1, _
Optional Compare As Integer = vbDatabaseCompare)

fReplace = VBA.Replace( _
Expression, Find, Replace, Start, Count, Compare)

End Function
'----- end of code -----

An alternative might be to apply the latest Access 2000 service pack,
which I *think* will allow you to call the Replace function directly
from your query.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.