removing spaces

[Helen]

I have two update queries, I have already asks about this,
but still not sure, 1st I want to create an update query
which removes the 3 extra spaces that are in the field
called Sentence.
2nd, I want to create update query that removes the extra
spaces at the end of the field called Sentence.
(I am using same table for both)

Thank you.
Helen

 

[John Spencer (MVP)]

What version of Access are you using?

This will trim off the spaces at the end.

UPDATE YourTable
Set Sentence = RTrim(Sentence)

In Access 2002 and later, you should be able to use:

UPDATE YourTable
Set Sentence = Replace(Sentence," ","")

Note: Do this on a copy of your data to see if you get the desired results.
This is NOT undo-able.

 

[Helen]

Hi John, and thank you. I am using Access 2002

-----Original Message-----
What version of Access are you using?

This will trim off the spaces at the end.

UPDATE YourTable
Set Sentence = RTrim(Sentence)

In Access 2002 and later, you should be able to use:

UPDATE YourTable
Set Sentence = Replace(Sentence," ","")

Note: Do this on a copy of your data to see if you get the desired results.
This is NOT undo-able.

.

 

[Helen]

Using Access 2002, I have a backup of database. I tried
what you suggested, however under my Sentence Field were
actual sentences, I tried RTrim (Sentence), after I
clicked on Run, I went to Datasheet view, under the
Sentence field it has the word Sentence in all rows. The
sentences that were there are gone. Also I tried RTrim
(Sentence, " ", " " got error message.

 

[John Vinson]

Using Access 2002, I have a backup of database. I tried
what you suggested, however under my Sentence Field were
actual sentences, I tried RTrim (Sentence), after I
clicked on Run, I went to Datasheet view, under the
Sentence field it has the word Sentence in all rows. The
sentences that were there are gone. Also I tried RTrim
(Sentence, " ", " " got error message.

Try

Rtrim([Sentence])

The square brackets tell Access to look in the field named sentence,
rather than trimming the text string "sentence"!

 

[Helen]

John, I am using Access 2002. In UPDATE Table I entered
Value, RTrim(Sentence). However under Sentence Field were
actual sentences in the Rows, when I clicked on run, and
then went to Datasheet view, Under the sentence Field was
the word Sentence, the previous sentences were gone. I do
have a back up of the database.

 

[John Vinson]

John, I am using Access 2002. In UPDATE Table I entered
Value, RTrim(Sentence). However under Sentence Field were
actual sentences in the Rows, when I clicked on run, and
then went to Datasheet view, Under the sentence Field was
the word Sentence, the previous sentences were gone. I do
have a back up of the database.

Use

RTrim([Sentence])

with the square brackets, to inform Access that you mean the fieldname
rather than the literal text string "sentence".