Modify numbers

[Golfinray]

I have numbers in a table that are all four digits, a dash, and then 3
digits, like 3534-252. I would like to add 0709- to the front of each number,
like 0709-3534-252. I have been trying the Replace command but I just can't
get it to work right. Thanks a bunch!!!!

 

[Mackster66]

There may be a more direct method, but this will do what you are looking for.

Add a new field to your table called TEMPNUMBER. Make sure the data type is
the same as the type for the field you want to update.

Create an update query based on the table. Make sure your original number
field and the TEMPNUMBER field are included in the query.

Right click in the 'update to' section of the TEMPNUMBER field and select
'build'. Double-click on the original number field (in the middle column)
and click ok. Run the query by clicking on the red exclamation point in the
top toolbar. This will copy all of the numbers over to the TEMPNUMBER field.

Now clear out the 'update to' section of the TEMPNUMBER field. Right click
in the 'update to' section of the original number field and select 'build'.
Cut and paste the following (including quotation marks):

"0709-" & [TEMPNUMBER]

Run the query again by clicking on the red exclamation point. You have now
added the 0709- to the beginning of all of the numbers. You can now go back
to the table and delete the TEMPNUMBER field.

 

[Dennis]

Create a query and select just that field in the query grid. Change the query
type to an update query and in the Update to Row put
"0709-" & [YourFieldName]

 

[Wayne-I-M]

Hi

If you're sure you want to change "all" the records then run an update query

UPDATE TableName SET TableName.NameOfField = "0709-" & TableName!NameOfField;


--
Wayne
Manchester, England.

There may be a more direct method, but this will do what you are looking for.

Add a new field to your table called TEMPNUMBER. Make sure the data type is
the same as the type for the field you want to update.

Create an update query based on the table. Make sure your original number
field and the TEMPNUMBER field are included in the query.

Right click in the 'update to' section of the TEMPNUMBER field and select
'build'. Double-click on the original number field (in the middle column)
and click ok. Run the query by clicking on the red exclamation point in the
top toolbar. This will copy all of the numbers over to the TEMPNUMBER field.

Now clear out the 'update to' section of the TEMPNUMBER field. Right click
in the 'update to' section of the original number field and select 'build'.
Cut and paste the following (including quotation marks):

"0709-" & [TEMPNUMBER]

Run the query again by clicking on the red exclamation point. You have now
added the 0709- to the beginning of all of the numbers. You can now go back
to the table and delete the TEMPNUMBER field.

--
I''m not young enough to know everything.

I have numbers in a table that are all four digits, a dash, and then 3
digits, like 3534-252. I would like to add 0709- to the front of each number,
like 0709-3534-252. I have been trying the Replace command but I just can't
get it to work right. Thanks a bunch!!!!

 

[Mackster66]

That is definitely a better way to go! I don't know why I didn't think of
that.

--
I''m not young enough to know everything.

Create a query and select just that field in the query grid. Change the query
type to an update query and in the Update to Row put
"0709-" & [YourFieldName]

I have numbers in a table that are all four digits, a dash, and then 3
digits, like 3534-252. I would like to add 0709- to the front of each number,
like 0709-3534-252. I have been trying the Replace command but I just can't
get it to work right. Thanks a bunch!!!!

 

[Tom van Stiphout]

On Wed, 18 Jun 2008 05:23:00 -0700, Golfinray

Why do that if you can simply add that part in a query:
select "0709-" & SomeField
from SomeTable

If you insist you can write an update query:
update SomeField
set SomeField = "0709-" & SomeField
from SomeTable

-Tom.