Removing empty space character in cell

I've got empty space in my records (ie "abcd ', "efgh " etc) I have tried to
use the find and replace but this is not working

I have also attempted to run an update query to update the cell from
"abcd " to "abcd" but when i do this it just takes out the info and replaces
with a '0'. I am not sure if i have the update to set incorrectly or what. i
have:

"SET myfield"=Replace("myfield"," ","")

On Fri, 18 Dec 2009 09:55:01 -0800, Booth wrote:

I've got empty space in my records (ie "abcd ', "efgh " etc) I have tried to
use the find and replace but this is not working

I have also attempted to run an update query to update the cell from
"abcd " to "abcd" but when i do this it just takes out the info and replaces
with a '0'. I am not sure if i have the update to set incorrectly or what. i
have:

"SET myfield"=Replace("myfield"," ","")

You've just given us a snippet of the Update SQL with misplaced and
mis-used quotes.
In a query, try it this way:

Update MyTable Set MyTable.[MyField] = Replace([MyField]," ","")

Replace MyTable and MyField with the correct table and field names.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

"fredg" wrote in message
...
On Fri, 18 Dec 2009 09:55:01 -0800, Booth wrote:

I've got empty space in my records (ie "abcd ', "efgh " etc) I have tried
to
use the find and replace but this is not working

I have also attempted to run an update query to update the cell from
"abcd " to "abcd" but when i do this it just takes out the info and
replaces
with a '0'. I am not sure if i have the update to set incorrectly or
what. i
have:

"SET myfield"=Replace("myfield"," ","")

You've just given us a snippet of the Update SQL with misplaced and
mis-used quotes.
In a query, try it this way:

Update MyTable Set MyTable.[MyField] = Replace([MyField]," ","")

Replace MyTable and MyField with the correct table and field names.

Actually, since it looks as though the spaces are at the end of the field
(which surprises me, because Access doesn't normally store trailing spaces),
it might be that all that's required is

Update MyTable Set MyTable.[MyField] = Trim([MyField])


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

when i try this it now just deletes all of the information in my cell? I just
want to delete the space at the end of my information

"fredg" wrote:

On Fri, 18 Dec 2009 09:55:01 -0800, Booth wrote:

I've got empty space in my records (ie "abcd ', "efgh " etc) I have tried to
use the find and replace but this is not working

I have also attempted to run an update query to update the cell from
"abcd " to "abcd" but when i do this it just takes out the info and replaces
with a '0'. I am not sure if i have the update to set incorrectly or what. i
have:

"SET myfield"=Replace("myfield"," ","")

You've just given us a snippet of the Update SQL with misplaced and
mis-used quotes.
In a query, try it this way:

Update MyTable Set MyTable.[MyField] = Replace([MyField]," ","")

Replace MyTable and MyField with the correct table and field names.
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
.

i just tried

Update MyTable Set MyTable.[MyField] = Trim([MyField])

and when i run the query it deletes all of the values and just leaves the
cells blank....any other thoughts?

"Douglas J. Steele" wrote:

"fredg" wrote in message
...
On Fri, 18 Dec 2009 09:55:01 -0800, Booth wrote:

I've got empty space in my records (ie "abcd ', "efgh " etc) I have tried
to
use the find and replace but this is not working

I have also attempted to run an update query to update the cell from
"abcd " to "abcd" but when i do this it just takes out the info and
replaces
with a '0'. I am not sure if i have the update to set incorrectly or
what. i
have:

"SET myfield"=Replace("myfield"," ","")

You've just given us a snippet of the Update SQL with misplaced and
mis-used quotes.
In a query, try it this way:

Update MyTable Set MyTable.[MyField] = Replace([MyField]," ","")

Replace MyTable and MyField with the correct table and field names.

Actually, since it looks as though the spaces are at the end of the field
(which surprises me, because Access doesn't normally store trailing spaces),
it might be that all that's required is

Update MyTable Set MyTable.[MyField] = Trim([MyField])


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)



.

That is strange since the VBA function only removes leading and trailing
spaces and nothing else

Just for grins try
Update MyTable Set MyTable.[MyField] = VBA.Trim([MyField])

Or if you want to see the result try a SELECT query and see what is returned

SELECT MyField & "//" as HasSpace
, Trim(MyField) & "//" as RemoveSpace
FROM MyTable

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Booth wrote:
i just tried

Update MyTable Set MyTable.[MyField] = Trim([MyField])

and when i run the query it deletes all of the values and just leaves the
cells blank....any other thoughts?

"Douglas J. Steele" wrote:

"fredg" wrote in message
...
On Fri, 18 Dec 2009 09:55:01 -0800, Booth wrote:

I've got empty space in my records (ie "abcd ', "efgh " etc) I have tried
to
use the find and replace but this is not working

I have also attempted to run an update query to update the cell from
"abcd " to "abcd" but when i do this it just takes out the info and
replaces
with a '0'. I am not sure if i have the update to set incorrectly or
what. i
have:

"SET myfield"=Replace("myfield"," ","")
You've just given us a snippet of the Update SQL with misplaced and
mis-used quotes.
In a query, try it this way:

Update MyTable Set MyTable.[MyField] = Replace([MyField]," ","")

Replace MyTable and MyField with the correct table and field names.
Actually, since it looks as though the spaces are at the end of the field
(which surprises me, because Access doesn't normally store trailing spaces),
it might be that all that's required is

Update MyTable Set MyTable.[MyField] = Trim([MyField])


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)



.