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]

Re: modify table field

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.c om

http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-access/200610/1

[tina]

Re: modify table field

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.Field Name = Right([TableName].[FieldName],3);

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

hth


"ken" <ken_d128@yahoo .comwrote in message
news:1162136084 .594543.86440@e 64g2000cwd.goog legroups.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]

Re: modify table field

"ken" <ken_d128@yahoo .comwrote in
news:1162136084 .594543.86440@e 64g2000cwd.goog legroups.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