Carriage Returns

[KentAust]

Still Problems with Carriage Returns

I am wondering if anyone can help me.

I have a field in a table that contains carriage returns.

Is there anyway the carriage returns can be removed leaving the remaining data in the field.

Thank you

eg Committed P/O 931102 Issue
Supplier: 277

the above is all in one field there is a carriage return between
the Issue and Supplier as this is all one string.

I am not sue how to extract and delete the carriage return to leave just.

Committed P/O 931102 Issue Supplier: 277

 

[Jon K]

A carriage return usually contains two characters: chr(13) for a carriage return and chr(10) for a linefeed.

Try this Select query (type in the SQL View of a new query, replacing with your table name and field name):-

SELECT FieldName, left(FieldName, instr(FieldName, chr(13) & chr(10))-1)
& mid(FieldName, instr(FieldName, chr(13) & chr(10))+2) as NewValue
FROM yourTable
WHERE instr(FieldName, chr(13) & chr(10))>0;


If no records were returned in the SELECT query, the field probably contains only the chr(13). Delete all the & chr(10) and change chr(13) & chr(10))+2) to chr(13))+1) and try again.


If the NewValues shown are what you intended (you may want to add a space & " " in front of & mid(FieldName,...), back up your table, create and run an Update query, setting FiledName = the expression in the Select query e.g.

UPDATE yourTable
SET FieldName = left(FieldName, instr(FieldName, chr(13) & chr(10))-1)
& " " & mid(FieldName, instr(FieldName, chr(13) & chr(10))+2)
WHERE instr(FieldName, chr(13) & chr(10))>0;

 

[doran_doran]

Search Helps...

I wanted to Thank Jon for his brilliant donation about carriage return.

A carriage return usually contains two characters: chr(13) for a carriage return and chr(10) for a linefeed.

My code is now working and i have a carriage return between two fields.

LastVisitor: [LastVisitorName] & Chr(13) & Chr(10) & [LastVisitedDate]