Deleting carriage returns

[Dave]

I need to strip out carriage returns from a memo field. Basically, one of
the commercial databases we have uses a memo field for the address, and what
I'd like to do is get rid of the carriage returns so I can have the address
on a single line (possibly separated by commas) in a different database. Any
suggestions gratefully received.

Apologies if you replied to this earlier, I think my ISP must have dropped
the newsgroup a week or two ago and has only just started to receive it
again.

Dave

[Allen Browne]

This should work in Access 2000, 2002 and 2003:

1. Create a query into this table.

2. Change it to an Update query. (Update on Query menu).
Access adds a new row to the grid.

3. Drag the memo (named MyField in this example) into the grid.
In the Update row, enter:
Replace([MyField], Chr(13) & Chr(10), ", ")

4. Run the query.

If you have problems in A2000, see:
http://support.microsoft.com/?id=225956

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dave" <no****@yourbus iness.com> wrote in message
news:41******** *************** @ptn-nntp-reader02.plus.n et...

I need to strip out carriage returns from a memo field. Basically, one of
the commercial databases we have uses a memo field for the address, and
what
I'd like to do is get rid of the carriage returns so I can have the
address
on a single line (possibly separated by commas) in a different database.
Any
suggestions gratefully received.

Apologies if you replied to this earlier, I think my ISP must have dropped
the newsgroup a week or two ago and has only just started to receive it
again.

[Dave]

Thanks Allen

I gave it a go exactly as you suggested, but it comes up with 'UNDEFINED
FUNCTION REPLACE IN EXPRESSION'. I know SQL can handle replace, but it
doesn't look as though Access recognises it within this particular case. I'm
using Access 2000 and the link you included refers to problems evaluating
time and date expressions.

Dave

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:41******** *************** @per-qv1-newsreader-01.iinet.net.au ...

This should work in Access 2000, 2002 and 2003:

1. Create a query into this table.

2. Change it to an Update query. (Update on Query menu).
Access adds a new row to the grid.

3. Drag the memo (named MyField in this example) into the grid.
In the Update row, enter:
Replace([MyField], Chr(13) & Chr(10), ", ")

4. Run the query.

If you have problems in A2000, see:
http://support.microsoft.com/?id=225956

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dave" <no****@yourbus iness.com> wrote in message
news:41******** *************** @ptn-nntp-reader02.plus.n et...

I need to strip out carriage returns from a memo field. Basically, one of
the commercial databases we have uses a memo field for the address, and
what
I'd like to do is get rid of the carriage returns so I can have the
address
on a single line (possibly separated by commas) in a different database.
Any
suggestions gratefully received.

Apologies if you replied to this earlier, I think my ISP must have dropped the newsgroup a week or two ago and has only just started to receive it
again.

[Allen Browne]

The kb article lists these functions as having the problem:
FormatCurrency( ),
FormatDateTime( ),
FormatNumber(),
FormatPercent() ,
InStrRev(),
MonthName(),
*Replace()*,
Round(),
StrReverse(),
WeekdayName()

The kb article suggests making a wrapper fucnction, and calling that
function instead of trying to use Replace(), in any context that requires
the Expression Service in A2000. The query context does use the ES.
Function MyReplace(strEx pression As String, _
strFind As String, _
strReplace As String, _
Optional lngStart As Long = 1, _
Optional lngCount As Long = -1) As String

MyReplace = Replace(strExpr ession, strFind, strReplace, lngStart,
lngCount)
End Function
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dave" <no****@yourbus iness.com> wrote in message
news:41******** *************** @ptn-nntp-reader02.plus.n et...

Thanks Allen

I gave it a go exactly as you suggested, but it comes up with 'UNDEFINED
FUNCTION REPLACE IN EXPRESSION'. I know SQL can handle replace, but it
doesn't look as though Access recognises it within this particular case.
I'm
using Access 2000 and the link you included refers to problems evaluating
time and date expressions.

Dave

"Allen Browne" <Al*********@Se eSig.Invalid> wrote in message
news:41******** *************** @per-qv1-newsreader-01.iinet.net.au ...

This should work in Access 2000, 2002 and 2003:

1. Create a query into this table.

2. Change it to an Update query. (Update on Query menu).
Access adds a new row to the grid.

3. Drag the memo (named MyField in this example) into the grid.
In the Update row, enter:
Replace([MyField], Chr(13) & Chr(10), ", ")

4. Run the query.

If you have problems in A2000, see:
http://support.microsoft.com/?id=225956

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dave" <no****@yourbus iness.com> wrote in message
news:41******** *************** @ptn-nntp-reader02.plus.n et...

>I need to strip out carriage returns from a memo field. Basically, one
>of
> the commercial databases we have uses a memo field for the address, and
> what
> I'd like to do is get rid of the carriage returns so I can have the
> address
> on a single line (possibly separated by commas) in a different
> database.
> Any
> suggestions gratefully received.
>
> Apologies if you replied to this earlier, I think my ISP must have dropped > the newsgroup a week or two ago and has only just started to receive it
> again.