By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,604 Members | 858 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,604 IT Pros & Developers. It's quick & easy.

modify table field

P: n/a
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

Oct 29 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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

Oct 29 '06 #2

P: n/a
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

Oct 29 '06 #3

P: n/a
"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

Oct 29 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.