How can I find and replace carriage returns in Access? 1

[leondurham2002]

How can I find and replace carriage returns in Access? An update query doesn't seem to work because I want to keep the rest of the information in the cell. I figured out how to select the records with carriage returns:

SELECT Table.Description
FROM Table
WHERE instr(Table.Description, chr(13))>13;

Is there anyway to do the equivalent of a find/replace in the SQL design view?

 

[mdthornton]

Not sure why you say > 13 in your example, > 0 would find any occurences of CR's. Unless you always have at least 12 characters to start with. Anyway, the following should work for you:

update Table set
description = iif(instr(description, chr(13))>0,mid(description, 1, instr(description, chr(13))-1)+ " " + mid(description, instr(description, chr(13))+1),description);

I've not tested this but you get the idea. Note that this changes the first CR to a space. If you have multiple CR you'll need to run the update multiple times. Best Regards,
Mike