Access Database- Split Field into 2 fields

I have an access database with a field called acct.     The field is varying lengths of numbers.
Based on the field "Application" I need to move the last 3 or 5 numbers at the end to another field that I can create in the DB.   For example, If Application is IRA and the account number is 111111111222, I need to move the 222 to a separate field and remove it from the account number field.  But the 111111111 could be from 4 to 9 characters long and still have a 3 digit "sub number".  I just need the 222 sub number removed.  I have about 200,000 records to manipulate.    I imported a text file into access to accomplish this but I am open to any other suggestions.  Thanks in advance for your help.

kgregarAsked:
Who is Participating?
 
MikeTooleConnect With a Mentor Commented:
Oops, missed a bit
update MyTable set SubAcct = Right(Acct,3), Acct = left(Acct, Len(Acct) - 3)
0
 
Rey Obrero (Capricorn1)Commented:
you can use the left, right or mid function

if the acct is  more than 9 digits you can simply use

mid([acct],10) to get the sub number

how is the acct formatted if there are less than 9 digits  ( 4-9 characters)?
0
 
Rey Obrero (Capricorn1)Commented:
if the three digits sub number is constant in length (3 digits), you can use

right([acct],3)   to get the three digit sub number what ever the lentgh of the acct numbet
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
MikeTooleCommented:
update MyTable set SubAcct = Right(Acct,3), Acct = left(Len(Acct) - 3)
0
 
Rey Obrero (Capricorn1)Commented:
and to get the acct number you can use the left function

left([acct],len([acct])-3)
0
 
kgregarAuthor Commented:
My table has many applications.  So I want to use the above just for one type, example IRA, then I need one with right(acct,5) for CLN, etc....  

can I run this from a query that has the applications defined?  thanks again.
0
 
Rey Obrero (Capricorn1)Commented:
you have to do that in a query..
0
 
kgregarAuthor Commented:
Sorry, I am so frustrated with this project I cannot think clearly.
 I am trying to write this so it checks the application for IRA then runs the above code.  I can get the query and I can get the above but I cannot get them together.  your help is appreciated.

0
 
Rey Obrero (Capricorn1)Commented:
explain  <so it checks the application for IRA >  in details
0
 
kgregarAuthor Commented:
I need this:  SELECT REPORT.BUS_PERIOD, REPORT.CUST_ID, REPORT.APPLICATION, REPORT.ACCT_NBR, REPORT.PRIM_ACCT_OWNER,  REPORT.SubNum
FROM REPORT
WHERE (((REPORT.APPLICATION)="IRA"));
then I need to do this:  update MyTable set SubAcct = Right(Acct,3), Acct = left(Acct, Len(Acct) - 3)
But I don't want to update the entire table, just the IRA part (with the above select statement).

thanks again.

0
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
so you want to update the field SubNum in table report

update report set subnum=right([ACCT_NBR],3), ACCT_NBR=left([ACCT_NBR],len([ACCT_NBR])-3)
where APPLICATION="IRA"
0
 
Rey Obrero (Capricorn1)Commented:
create a back up of your table before  running the update query
0
 
kgregarAuthor Commented:
Thank you!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.