GETTING RID OF CARRIAGE RETURN/LINE FEED IN MEMO FIELD 1

[franksirvent]

Hi there
I have a db which I convert to an Excel spreadsheet.

the thing is that one of the fields is a Memo field and can have many lines (separated by carriage returns/line feeds).

When the information gets transfered to Excel sheet, each line from the memo field has a vertical line at the end, obviously related to the carriage return/line feed.

I tried to to take it out like this:

txtfromemofield = Forms![Quotes]![memofield]
For l = 1 To Len(txtfrommemofield)

If Asc(Mid(txtfrommemofield, l, 1)) = 10 Then
Mid(txtfrommemofield, l, 1) = ""
End If
Next l

but it doesn´t work. I´ve also tried asc=13 and nothing happens...

Any ideas ?

thanks in advance

 

[psemianonymous]

Use the replace function:

txtfrommemofield = Replace(Forms![Quotes]![memofield],vbcrlf,"&quot --
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

 

[franksirvent]

THANKS SO MUCH!
WORKS GREAT!!!

I just had to replace vbcrlf with vbCr so I still get the next line, but without the LF character...

thanks again

 

[pkhoo]

Hi foolio12/franksirvent,

I need to use this too to remove the carriage return from the table itself. how do I do it? (access 97/2000)

don't seem to be able to use the replace function in a report or query????

any help will be much appreciated. thank you

pk

 

[andykent]

I found the answer on google, posted by arnie@pepper.net (arnie@pepper.net) which helped me!



You can give this a shot. I had the opposite problem. I had to find carriage returns and replace with another character.

Like you I tried entering a carriage return through the find and replace command when viewing the table. Instead, I had to create a macro with the open table and find record actions. I set the find what field to =Chr$(13) & Chr$(10). I ran the macro and closed it down. I went back to view the table, selected the find and replace command, and in the find field were a bunch of lines and boxes which I guess represented the carriage returns. I entered in my replace criteria, ran it, and IT WORKED.

Basically I didn't care about the results of the macro. I just needed a way for the carriage return expression to be moved from the macro to the find and replace command.

You may be able to copy and paste whatever gets represented
in the find field to the replace field after you create and run a similar macro.

 

[psemianonymous]

Access 97 doesn't have a replace command, so you can build one yourself. Search for "basReplace" or "Replace function" or something similar--it's quite heavily requested.

Then you can use the "Replace" criteria.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm