field

[Jessica]

Hello All.


I have a table with well over 12,000 records. In my primary key field I have
numbers for example 12345 67890. My question is, is there a way to eliminate
the space so that it would read 1234567890. I've changed so far atleast 500
records without a space and figured I would post to see if there were an
easier way.


TIA

 

[Brendan Reynolds]

You can do it with an update query ...

UPDATE tblTest SET TestText = Replace([TestText]," ","");

Replace 'tblTest' with the name of your table, and 'TestText' with the name
of your field.

In case it's not clear in the newsgroup post - the first set of quotes has a
space between the quotes, the second set of quotes has no space.

 

[Jessica]

Hi Brendan,


I did this but every record resulted in a zero after I updated. Did I do
something wrong?

You can do it with an update query ...

UPDATE tblTest SET TestText = Replace([TestText]," ","");

Replace 'tblTest' with the name of your table, and 'TestText' with the
name of your field.

In case it's not clear in the newsgroup post - the first set of quotes has
a space between the quotes, the second set of quotes has no space.

--
Brendan Reynolds

Hello All.


I have a table with well over 12,000 records. In my primary key field I
have numbers for example 12345 67890. My question is, is there a way to
eliminate the space so that it would read 1234567890. I've changed so far
atleast 500 records without a space and figured I would post to see if
there were an easier way.


TIA

 

[Brendan Reynolds]

Could you post the SQL from your query?

--
Brendan Reynolds

Hi Brendan,


I did this but every record resulted in a zero after I updated. Did I do
something wrong?

You can do it with an update query ...

UPDATE tblTest SET TestText = Replace([TestText]," ","");

Replace 'tblTest' with the name of your table, and 'TestText' with the
name of your field.

In case it's not clear in the newsgroup post - the first set of quotes
has a space between the quotes, the second set of quotes has no space.

--
Brendan Reynolds

Hello All.


I have a table with well over 12,000 records. In my primary key field I
have numbers for example 12345 67890. My question is, is there a way to
eliminate the space so that it would read 1234567890. I've changed so
far atleast 500 records without a space and figured I would post to see
if there were an easier way.


TIA

 

[Jessica]

UPDATE tblFormsDetail SET tblFormsDetail.strProductID = "tblFormsDetail SET
strProductID"=Replace([strProductID]," ","");

Could you post the SQL from your query?

--
Brendan Reynolds

Hi Brendan,


I did this but every record resulted in a zero after I updated. Did I do
something wrong?

You can do it with an update query ...

UPDATE tblTest SET TestText = Replace([TestText]," ","");

Replace 'tblTest' with the name of your table, and 'TestText' with the
name of your field.

In case it's not clear in the newsgroup post - the first set of quotes
has a space between the quotes, the second set of quotes has no space.

--
Brendan Reynolds

Hello All.


I have a table with well over 12,000 records. In my primary key field I
have numbers for example 12345 67890. My question is, is there a way to
eliminate the space so that it would read 1234567890. I've changed so
far atleast 500 records without a space and figured I would post to see
if there were an easier way.


TIA

 

[Douglas J. Steele]

That should be

UPDATE tblFormsDetail SET tblFormsDetail.strProductID = '" &
Replace([strProductID]," ","") & "'"

Exagerated for clarity, that's

UPDATE tblFormsDetail SET tblFormsDetail.strProductID = ' " &
Replace([strProductID]," ","") & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

UPDATE tblFormsDetail SET tblFormsDetail.strProductID = "tblFormsDetail
SET strProductID"=Replace([strProductID]," ","");

Could you post the SQL from your query?

--
Brendan Reynolds

Hi Brendan,


I did this but every record resulted in a zero after I updated. Did I do
something wrong?

You can do it with an update query ...

UPDATE tblTest SET TestText = Replace([TestText]," ","");

Replace 'tblTest' with the name of your table, and 'TestText' with the
name of your field.

In case it's not clear in the newsgroup post - the first set of quotes
has a space between the quotes, the second set of quotes has no space.

--
Brendan Reynolds

Hello All.


I have a table with well over 12,000 records. In my primary key field
I have numbers for example 12345 67890. My question is, is there a way
to eliminate the space so that it would read 1234567890. I've changed
so far atleast 500 records without a space and figured I would post to
see if there were an easier way.


TIA

 

[Jessica]

Thanks Brendan and Douglas for you replies. I entered in the new statement
and it would not allow me to run the query. It says the expression has an
invalid string.

That should be

UPDATE tblFormsDetail SET tblFormsDetail.strProductID = '" &
Replace([strProductID]," ","") & "'"

Exagerated for clarity, that's

UPDATE tblFormsDetail SET tblFormsDetail.strProductID = ' " &
Replace([strProductID]," ","") & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

UPDATE tblFormsDetail SET tblFormsDetail.strProductID = "tblFormsDetail
SET strProductID"=Replace([strProductID]," ","");

Could you post the SQL from your query?

--
Brendan Reynolds


Hi Brendan,


I did this but every record resulted in a zero after I updated. Did I
do something wrong?

message You can do it with an update query ...

UPDATE tblTest SET TestText = Replace([TestText]," ","");

Replace 'tblTest' with the name of your table, and 'TestText' with the
name of your field.

In case it's not clear in the newsgroup post - the first set of quotes
has a space between the quotes, the second set of quotes has no space.

--
Brendan Reynolds

Hello All.


I have a table with well over 12,000 records. In my primary key field
I have numbers for example 12345 67890. My question is, is there a
way to eliminate the space so that it would read 1234567890. I've
changed so far atleast 500 records without a space and figured I
would post to see if there were an easier way.


TIA

 

[Brendan Reynolds]

UPDATE tblFormsDetail SET tblFormsDetail.strProductID =
Replace([strProductID]," ","");

Here's what it looks like in query design view ...

http://brenreyn.brinkster.net/reqquery.jpg

--
Brendan Reynolds

Thanks Brendan and Douglas for you replies. I entered in the new statement
and it would not allow me to run the query. It says the expression has an
invalid string.

That should be

UPDATE tblFormsDetail SET tblFormsDetail.strProductID = '" &
Replace([strProductID]," ","") & "'"

Exagerated for clarity, that's

UPDATE tblFormsDetail SET tblFormsDetail.strProductID = ' " &
Replace([strProductID]," ","") & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

UPDATE tblFormsDetail SET tblFormsDetail.strProductID = "tblFormsDetail
SET strProductID"=Replace([strProductID]," ","");

Could you post the SQL from your query?

--
Brendan Reynolds


Hi Brendan,


I did this but every record resulted in a zero after I updated. Did I
do something wrong?

message You can do it with an update query ...

UPDATE tblTest SET TestText = Replace([TestText]," ","");

Replace 'tblTest' with the name of your table, and 'TestText' with
the name of your field.

In case it's not clear in the newsgroup post - the first set of
quotes has a space between the quotes, the second set of quotes has
no space.

--
Brendan Reynolds

Hello All.


I have a table with well over 12,000 records. In my primary key
field I have numbers for example 12345 67890. My question is, is
there a way to eliminate the space so that it would read 1234567890.
I've changed so far atleast 500 records without a space and figured
I would post to see if there were an easier way.


TIA

 

[Jessica]

Thanks Brendan that worked and what a time saver.

Jess

UPDATE tblFormsDetail SET tblFormsDetail.strProductID =
Replace([strProductID]," ","");

Here's what it looks like in query design view ...

http://brenreyn.brinkster.net/reqquery.jpg

--
Brendan Reynolds

Thanks Brendan and Douglas for you replies. I entered in the new
statement and it would not allow me to run the query. It says the
expression has an invalid string.

That should be

UPDATE tblFormsDetail SET tblFormsDetail.strProductID = '" &
Replace([strProductID]," ","") & "'"

Exagerated for clarity, that's

UPDATE tblFormsDetail SET tblFormsDetail.strProductID = ' " &
Replace([strProductID]," ","") & " ' "

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



UPDATE tblFormsDetail SET tblFormsDetail.strProductID = "tblFormsDetail
SET strProductID"=Replace([strProductID]," ","");

message Could you post the SQL from your query?

--
Brendan Reynolds


Hi Brendan,


I did this but every record resulted in a zero after I updated. Did I
do something wrong?

message You can do it with an update query ...

UPDATE tblTest SET TestText = Replace([TestText]," ","");

Replace 'tblTest' with the name of your table, and 'TestText' with
the name of your field.

In case it's not clear in the newsgroup post - the first set of
quotes has a space between the quotes, the second set of quotes has
no space.

--
Brendan Reynolds

Hello All.


I have a table with well over 12,000 records. In my primary key
field I have numbers for example 12345 67890. My question is, is
there a way to eliminate the space so that it would read
1234567890. I've changed so far atleast 500 records without a space
and figured I would post to see if there were an easier way.


TIA