Replace Space + Return with Return

quick and dirty, but not accurate, since the number of spaces is not known
run this query


Update AllData
set data1=replace(replace(replace(replace([data1], space(5),space(1)), space(4),space(1)), space(3),space(1)), space(2),space(1))


you may have to run it more than once..

Thanks capricorn1  The wording of my question was poor and confusing.
(I haven't tried your code yet)

I don't have hundreds of spaces in a row ... I have hundreds of ...

Text
Text
single space + return
Text
single space + return
Text
Text
return
Text
single space + return
single space + return
Text
Text
single space + return
Text
Return
Text
Text
single space + return
Text

And I want to eliminate all the single spaces where it is followed by a return

ok try this

Update AllData
set data1= replace([data1], chr(32) & chr(13) & chr(10),chr(13) & chr(10))

if this doesn't work, upload a sample db with the table.. AllData

My table is called VOCAB and the first field I'm working on is called DEFINITION

Nothing changes when I run this (I do get an error)

    DoCmd.RunSQL "Update VOCAB Set DEFINITION = Replace([DEFINITION], Chr(32) & Chr(13) & Chr(10), Chr(13) & Chr(10))"

Is the above correct? I'll post a DB if necessary.

======================================================= EDIT ======
This is strange .... just before I clicked SUBMIT on this post I manually deleted some of the SPACES .... but they keep coming back after I save .... It seem to be a 'feature' of memo fields.

I manually deleted a few hundred spaces yesterday, but they are all back now.


They are only a minor nuisance and I can live with them.

yes, that is correct..

yes, better upload a sample db with the table.

what version of Access are you using?

 If A2007 or above, check the memo field property "Append Only" setting.
If it is YES then, you can not edit the content.

I'm using access 2010 and Append only is NO on all memo fields.

DELETE SPACES on the switchboard form runs your code.

Here is my vocab database (a work in progress) of familiar words (or that ring a bell) but don't I use in conversation or writing. There no personal info so it's here in it's entirety.

Go to the second word on VOCAB A-D (ABROGATE)

Repeal or do away with (a law, right, or formal agreement)
SPACE + RETURN
To treat as non-existent (abrogating their responsibilities)

How do I get rid of the pesky SPACE

Most of the content is pasted from the web and edited.
GENERAL.accdb
GENERAL-TABLES.accdb

Looks like your information came from a web page, is this correct?

here is the content of record 89 (abrogate)


<div>Repeal or do away with (a law, right, or formal agreement)</div>

<div>&nbsp;</div>

<div>To treat as non-existent (abrogating their responsibilities)</div>


.

As I said .....
Most of the content is pasted from the web and edited.

how do you want this content to look like

<div>Repeal or do away with (a law, right, or formal agreement)</div>

<div>&nbsp;</div>

<div>To treat as non-existent (abrogating their responsibilities)</div>

I think this is what I want ... A simple return with no preceding space.


<div>Repeal or do away with (a law, right, or formal agreement)</div>
<div>
<div>To treat as non-existent (abrogating their responsibilities)</div>

don't you want it to be just like this

Repeal or do away with (a law, right, or formal agreement)
 
To treat as non-existent (abrogating their responsibilities)

That's the way it looks at present .... the only thing is there is a nuisance space between the two lines. Try deleting the space within the database ..... it's gone .... click on another record and go back ..... the space is back!

The invisible space is no problem until I want to enter another line of text .... I have to backspace before typing.

Thanks for your persistence in this. The only way to see the problem is within the database itself

EDIT ...

Repeal or do away with (a law, right, or formal agreement)
 
To treat as non-existent (abrogating their responsibilities)


The above has a space between the lines (Highlight it) .... I want to eliminate the space.

Thanks capricorn1, I'll try it over the weekend and post back here on Monday.

Just reading your accompanying text
query one may do what I want, query two won't as it removes the CR

Belated Points!
Somehow, this question seemed to get lost in the system.