Removing cf and lf characters from a Field

[porto99]

Having imported data from a FoxPro 2.6 database into this Access 2003 database, I need to remove the cr and lf from what once was the Memo fields.

I am sure its simple, but please give a simple example of how to process fields with Visual Basic in the database.

Cheers,

Porto

 

[Remou]

Try Replace.
Replace (MemoField, (Chr(13)+Chr(10)), " ")
Or

Replace (MemoField, Chr(13), " ")
And
Replace (MemoField, Chr(10), " ")

 

[porto99]

Thanks for your post.

Will the above commands run through the whole database when entered in from the FoxPro Command Window.

Sorry but not a FoxPro user.

 

[Golom]

Remou has given you the function (Replace) that you should use. It is just a function that returns a value. It does not "run through" anything.

You mention the FoxPro Command Window but, since you said that the table had been imported into Access, these are Access commands and you would incorporate them in VBA code in Access or an Access SQL statement.

If you want the CR and/or LF characters permanently removed then

UPDATE myTable SET [MemoField] =
  Replace(Replace([MemoField],Chr(13)," "),Chr(10)," ")

will remove CR and/or LF characters from the field [MemoField] in the table myTable.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Remou]

From your post, it seemed as if you had already imported the data to Access and wished to alter the memo field in Access. If you need a Fox Pro solution, you could try the Fox Pro forum.
The above could be used in an Access query to update the entire database. For example:
[tt]UPDATE Table1 SET Table1.MyMemo = Replace(MyMemo,(Chr(13)+Chr(10))," ");[/tt]
However, this will not work in Access 97.

 

[porto99]

Hi, sorry for the comfusion. I did think I had it working, but I do need to remove the cr/lf from the Memo fields in FoxPro. I did think the import to Access went OK, but later found problems.

However as FoxPro 2.6 is so old any forums are it seems not being used much.