Remove linefeed characters from memo fields 4

[Mulle]

I'm sure theres an easy answer to this but I can't find it anywhere so here goes...
I have an Access database from which I'm in the process of producing an extract that will then be migrated into a Siebel application. The problem I'm having is that the users have been copying and pasting data into some memo fields from other applications which include linefeed characters, this has resulted in a number of errors in the extract as it's starting a new row in the middle of the text. All I need to do is replace the linefeed character with a space but I can't seem to find a way of using the CHR(10) or ascii codes in a replace.
I'm probably being really dumb but any help would be greatly appreciated!!
Thanks!

Mulle

 

[Frink]

Hallo,

This code would do it:

public function strConvertLFToSpace (byref pstrString as string) as string
  dim lngPtr as long
  dim strResult as string

  strResult ""
  for lngPtr = 1 to len(pstrString)
    if mid$(pstrString,lngPtr,1) = chr$(10) then
      strResult = strResult & " "
    else
      strResult = strResult & mid$(pstrString,lngPtr,1)
    end if
  next lngPtr
  strConvertLFToSpace = strResult 
end sub

To use it to update existing data in a table use an update query. Set the new value of <myfield> to strConvertLFToSpace(<myfield>)
Make sure you take a copy of your table before running the query as it might destroy your data

- Frink

 

[Mulle]

Thanks, that works but it's inserted a square character (don't know what it's called sorry!) where the linefeed was so if you open it in Excel it starts a new row and Siebel won't accept that either.

 

[Maquis]

You said that the memo field contains linefeed characters, but it's probably more accurate to say that it contains carriage returns and line feed characters.

You probably need to replace chr$(13) and chr$(10) with spaces. Maq
<insert witty signature here>

 

[Frink]

Hallo,

Maquis is right, windows uses CR LF to terminate lines.

If you replace

if mid$(pstrString,lngPtr,1) = chr$(10) then

with

if mid$(pstrString,lngPtr,1) = chr$(10) or mid$(pstrString,lngPtr,1) = chr$(13) then

it should work fine.
Sorry if this is teaching granny to suck eggs, but I don't want to assume too much. The code is not the most efficient, but there we go, it'll do (hopefully)

- Frink

 

[Mulle]

Cheers everyone, I put the or statement in and its perfect now. Thank heavens for Tek Tips!!

 

[Frink]

Hallo,

What

s Maquis and I are.

- Frink

P.S. Maq, It's worth a try

 

[Maquis]

LOL, Frink! I never thought of that. Good idea! Maq
<insert witty signature here>