Prev: Can Grow Property
Next: previous record not showing
From: Nich on 27 Apr 2010 11:20
I imported data which contained cr/lf or char(13). How do I find these
characters and strip them out?
From: kc-mass on 27 Apr 2010 11:40
You look for CHR(13) and CHR(10) with instr() function to
find and left, right and mid functions to remove.

Regards

Kevin


"Nich" <Nich(a)discussions.microsoft.com> wrote in message
news:EE61074F-888D-4520-8893-8ED754264AEB(a)microsoft.com...
>I imported data which contained cr/lf or char(13). How do I find these
> characters and strip them out?


From: Jerry Whittle on 27 Apr 2010 12:01
First do a backup of the database just in case things go wrong.

UPDATE YourTable
SET YourTable.YourField
= Replace([YourTable]![YourField],Chr(13)," ");

The above will put a space in place of the Chr(13).

Also Chr(10) and Chr(13) often work together. You may have to find both.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Nich" wrote:

> I imported data which contained cr/lf or char(13). How do I find these
> characters and strip them out?
From: John W. Vinson on 27 Apr 2010 12:08
On Tue, 27 Apr 2010 08:20:02 -0700, Nich <Nich(a)discussions.microsoft.com>
wrote:

>I imported data which contained cr/lf or char(13). How do I find these
>characters and strip them out?

That's curious: I just answered an almost identical question from "Dave".
Here's what I told him, which you should be able to adapt:

You'll need to find out what it is. My guess is that it's a linefeed Chr(10) -
it wouldn't hurt to make a backup of your database just in case, and run a
query

SELECT memofield, InStr([memofield], Chr(10))
FROM tablename
WHERE memofield LIKE "*" & Chr(10) & "*"

to see if it shows the records with the box and the position of the linefeed.
Count characters and see if it matches.

You could then run an Update query updating the memo field:

UPDATE tablename
SET memofield = Replace([memofield], Chr(10), "")

Note that if the field has legitimate carriage return-linefeed characters this
will do more harm than good!

--

John W. Vinson [MVP]
 | 
Pages: 1
Prev: Can Grow Property
Next: previous record not showing