modify table field

[ken]

I have a field in table "Jobs" called "JobNo" consisting of 6 numbers
such as 405043
I need to somehow delete the first 3 numbers to have field just be
043
I could delete the first 3 numbers by hand but have 9600 records in
table

Thanks for any and all suggestions
Ken

[Granny Spitz via AccessMonster.com]

ken wrote:

I need to somehow delete the first 3 numbers to have field just be
043

Make a backup of your table first, just in case something goes wrong. Run an
update query like this:

UPDATE Jobs
SET JobNo = Mid(JobNo, 4);

The leading zero won't stay unless this is a text column.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200610/1

[tina]

first, make a COPY of your database, and use the COPY for the next steps
(always pays to have a backup, when you're making changes to data en masse).

use an Update query to make the change, as

UPDATE TableName SET TableName.FieldName = Right([TableName].[FieldName],3);

replace TableName and FieldName with the correct table and field names, of
course.

hth
"ken" <ke******@yahoo.comwrote in message
news:11*********************@e64g2000cwd.googlegro ups.com...

I have a field in table "Jobs" called "JobNo" consisting of 6 numbers
such as 405043
I need to somehow delete the first 3 numbers to have field just be
043
I could delete the first 3 numbers by hand but have 9600 records in
table

Thanks for any and all suggestions
Ken

[Bob Quintal]

"ken" <ke******@yahoo.comwrote in
news:11*********************@e64g2000cwd.googlegro ups.com:

I have a field in table "Jobs" called "JobNo" consisting of 6
numbers such as 405043
I need to somehow delete the first 3 numbers to have field
just be 043
I could delete the first 3 numbers by hand but have 9600
records in table

Thanks for any and all suggestions
Ken

Well if you have 9600 records, and only three digits, jobno will
have duplicates. That may be a problem in itself.

If JobNo is a text field, just use the right() function to
return the three rightmost digits.
If JobNo is truly a number, you can either convert to text
before and after or calculate the remainder from 1000
use the mod operator for this
Make a backup table to test first, then build an update query
with the replace set to ([Jobno] mod 1000)
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com