detabify a memo field?

[Mark G. Meyers]

I have pasted in a bunch of text into a memo field of a table (100 records),
and am wondering what the easiest way would be to convert each TAB to a
SPACE in that table?

Thanks!

 

[Douglas J. Steele]

Assuming you're using Access 2000 or newer, use the Replace function:

Replace(MemoField, Chr$(9), " ")

If you're trying to do this in a query, and you're using certain versions of
Access 2000, you'll need to write your own function that wraps around the
Replace function. In other words, put a function like the following in a
module:

ReplaceTab(InputField As Variant) As String
ReplaceTab = Replace(InputField & vbNullString, Chr$(9), " ")
End Function

then use ReplaceTab in your query.

(The reason for having InputField as a variant is to handle any cases where
the MemoField is Null)

 

[Mark G. Meyers]

Thanks, Douglas!

Where do you put the Replace(), if you want it to do the replace for all the
records in the table?

 

[Douglas J. Steele]

Use an Update query.

Put the Replace statement under the field that you want to update, on the
row labelled "Update To".

In SQL, this would look like:

UPDATE MyTable
SET MyField = Replace([MyField], Chr$(9), " ")

 

[Mark G. Meyers]

Thanks!

Use an Update query.

Put the Replace statement under the field that you want to update, on the
row labelled "Update To".

In SQL, this would look like:

UPDATE MyTable
SET MyField = Replace([MyField], Chr$(9), " ")

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

Thanks, Douglas!

Where do you put the Replace(), if you want it to do the replace for all the
records in the table?

Assuming you're using Access 2000 or newer, use the Replace function:

Replace(MemoField, Chr$(9), " ")

If you're trying to do this in a query, and you're using certain

versions

of
Access 2000, you'll need to write your own function that wraps around the
Replace function. In other words, put a function like the following in a
module:

ReplaceTab(InputField As Variant) As String
ReplaceTab = Replace(InputField & vbNullString, Chr$(9), " ")
End Function

then use ReplaceTab in your query.

(The reason for having InputField as a variant is to handle any cases where
the MemoField is Null)


--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


"Mark G. Meyers" <mgmeyers[at]bellsouth.net> wrote in message
I have pasted in a bunch of text into a memo field of a table (100
records),
and am wondering what the easiest way would be to convert each TAB

to

a