Attempting to clean a large table in Access 2000, I've replaced thousands of
old HTML line-break tags with VBA line breaks, using an Update Query, as in:

Replace ([Text]), "<BR><BR>", Chr(13) & Chr(10) & Chr(10))

In many cases these line breaks have added to each other, so now I'm left
with hundreds of line breaks too many. Where three or more VBA line breaks
follow each other one after another, I have to reduce each group of three,
four, or five Chr(10)s to just two at a time, leaving a standard single-line
space between each paragraphs.

This is my first attempt to use any programming language at all, so I need
very basic help.

With thanks,

Philip

On Wed, 9 Feb 2005 22:53:01 -0800, "PAGA"
<(E-Mail Removed)> wrote:

>Attempting to clean a large table in Access 2000, I've replaced thousands of
>old HTML line-break tags with VBA line breaks, using an Update Query, as in:
>
>Replace ([Text]), "<BR><BR>", Chr(13) & Chr(10) & Chr(10))
>
>In many cases these line breaks have added to each other, so now I'm left
>with hundreds of line breaks too many. Where three or more VBA line breaks
>follow each other one after another, I have to reduce each group of three,
>four, or five Chr(10)s to just two at a time, leaving a standard single-line
>space between each paragraphs.
>
>This is my first attempt to use any programming language at all, so I need
>very basic help.
>
replace 3 "lineBreaks" with two and run a few times

---
If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW

I've tried that. It doesn't seem to work.

Is this what you mean? --


Replace ([Text]), Chr(10) & Chr(10) & Chr(10), Chr(10) & Chr(10))


"Andi Mayer" wrote:

> On Wed, 9 Feb 2005 22:53:01 -0800, "PAGA"
> <(E-Mail Removed)> wrote:
>
> >Attempting to clean a large table in Access 2000, I've replaced thousands of
> >old HTML line-break tags with VBA line breaks, using an Update Query, as in:
> >
> >Replace ([Text]), "<BR><BR>", Chr(13) & Chr(10) & Chr(10))
> >
> >In many cases these line breaks have added to each other, so now I'm left
> >with hundreds of line breaks too many. Where three or more VBA line breaks
> >follow each other one after another, I have to reduce each group of three,
> >four, or five Chr(10)s to just two at a time, leaving a standard single-line
> >space between each paragraphs.
> >
> >This is my first attempt to use any programming language at all, so I need
> >very basic help.
> >
> replace 3 "lineBreaks" with two and run a few times
>
> ---
> If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
> MW
>

On Thu, 10 Feb 2005 03:01:05 -0800, "PAGA"
<(E-Mail Removed)> wrote:

>I've tried that. It doesn't seem to work.
>
>Is this what you mean? --
>
>
>Replace ([Text]), Chr(10) & Chr(10) & Chr(10), Chr(10) & Chr(10))
>

chr(10)=line Feed
chr(13)=carriage return

whats in your data? both or only line feeds?

---
If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
MW

Pardon...I think it would be good to replace 2 "line break" combinations
with one "line break" so that all double/multiple line breaks are converted
to single ones.
:-)
--

Ken Snell
<MS ACCESS MVP>

"Andi Mayer" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> On Wed, 9 Feb 2005 22:53:01 -0800, "PAGA"
> <(E-Mail Removed)> wrote:
>
>>Attempting to clean a large table in Access 2000, I've replaced thousands
>>of
>>old HTML line-break tags with VBA line breaks, using an Update Query, as
>>in:
>>
>>Replace ([Text]), "<BR><BR>", Chr(13) & Chr(10) & Chr(10))
>>
>>In many cases these line breaks have added to each other, so now I'm left
>>with hundreds of line breaks too many. Where three or more VBA line breaks
>>follow each other one after another, I have to reduce each group of three,
>>four, or five Chr(10)s to just two at a time, leaving a standard
>>single-line
>>space between each paragraphs.
>>
>>This is my first attempt to use any programming language at all, so I need
>>very basic help.
>>
> replace 3 "lineBreaks" with two and run a few times
>
> ---
> If you expect an answer to a personal mail, add the word "manfred" to the
> first 10 lines in the message
> MW

Andy,

Every space between pars in my database Text field is created by a carriage
return chr(13), followed by two linefeeds, chr(10) & chr(10). The combination
of these three inserts a space as follows:

But thousands of records have much larger spaces between pars, creating gaps
which look like this, or larger:



I presume this is because the carriage return is followed by more than two
linefeeds, chr(10). But I don't know if this is true, or if the extra spaces
between pars could contain both chr(13) and chr(10) codes. I have no idea how
to check which is the case.

Assuming that all the extra spaces between pars are caused by superfluous
chr(10) linefeeds, I tried this update query:

Replace ([Text]), Chr(10) & Chr(10) & Chr(10), Chr(10) & Chr(10))

I figured this would replace three Chr(10)s with just two -- but it doesn't.

Is there a flaw in my query string?

Thanks,


"Andi Mayer" wrote:

> On Thu, 10 Feb 2005 03:01:05 -0800, "PAGA"
> <(E-Mail Removed)> wrote:
>
> >I've tried that. It doesn't seem to work.
> >
> >Is this what you mean? --
> >
> >
> >Replace ([Text]), Chr(10) & Chr(10) & Chr(10), Chr(10) & Chr(10))
> >
>
> chr(10)=line Feed
> chr(13)=carriage return
>
> whats in your data? both or only line feeds?
>
> ---
> If you expect an answer to a personal mail, add the word "manfred" to the first 10 lines in the message
> MW
>

Ken,

I've posted my message below to Andy, who tried to help me with this, but
I'm not sure how to make sure you see it as well -- except to send you
another copy (below).

The query string I used doesn't work, so I'm trying to find out where I went
wrong.

Thanks,

PAGA

"Ken Snell [MVP]" wrote:

> Pardon...I think it would be good to replace 2 "line break" combinations
> with one "line break" so that all double/multiple line breaks are converted
> to single ones.
> :-)
> --
>
> Ken Snell
> <MS ACCESS MVP>
>
> "Andi Mayer" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > On Wed, 9 Feb 2005 22:53:01 -0800, "PAGA"
> > <(E-Mail Removed)> wrote:
> >
> >>Attempting to clean a large table in Access 2000, I've replaced thousands
> >>of
> >>old HTML line-break tags with VBA line breaks, using an Update Query, as
> >>in:
> >>
> >>Replace ([Text]), "<BR><BR>", Chr(13) & Chr(10) & Chr(10))
> >>
> >>In many cases these line breaks have added to each other, so now I'm left
> >>with hundreds of line breaks too many. Where three or more VBA line breaks
> >>follow each other one after another, I have to reduce each group of three,
> >>four, or five Chr(10)s to just two at a time, leaving a standard
> >>single-line
> >>space between each paragraphs.
> >>
> >>This is my first attempt to use any programming language at all, so I need
> >>very basic help.
> >>
> > replace 3 "lineBreaks" with two and run a few times
> >
> > ---
> > If you expect an answer to a personal mail, add the word "manfred" to the
> > first 10 lines in the message
> > MW
>
>
>

"=?Utf-8?B?UEFHQQ==?=" <(E-Mail Removed)> wrote in
news:4B106D47-A24F-4B95-95AE-(E-Mail Removed):

>
> Assuming that all the extra spaces between pars are caused by
> superfluous chr(10) linefeeds, I tried this update query:
>
>

Not tested, but this should do it:

Public Function RemoveLF(SomeString As String) As String
Dim p As Integer '
' find a Lf
p = Instr(SomeString,vbLf) ' vbLf is a Chr$(10)
If p = 0 then
' nothing to do
RemoveLF = SomeString

Else
' remove it, and any others
RemoveLF = RemoveLF(Left$(SomeString,p-1) & Mid$(SomeString,p+1))

End If

End Function


The other thing is to use the reg exp object to do it.

HTH


Tim F