Seperating a field into two 1

[paul123456]

I have one field as text with 2 letters followed by numbers. how can i cut out the first two characters of each record and put them in another field and leave the remaining characters in the original field??

here are the records i will be using

IDNum
StateCode

The data right now is in the IDnum field like this

TX234
TX454654
TX234

how can i take TX and move it to State code without changint the remaining numbers?

Thanks, PAUL

 

[Golom]

I assume that you want, for example

IDNum = 234
StateCode = TX

after the update.
[tt]
UPDATE tbl SET IDNum = Mid(IDNum,3), StateCode = Left(IDNum,2)
[/tt]

 

[mgolla]

Use the Left statement to extract the first two characters to put into a different field.

Example:

MyStateField: Left([IDnum],2)



HTH
Mike

Dooobie...Doobie......Dooo

Beware the penguins

 

[paul123456]

Hey thanx that worked great. How would you delete the first 2 characters in the IDNum field?

Thanks, PAUL

 

[thecreator]

Use just the first part of Golom's code -

UPDATE tbl SET IDNum = Mid(IDNum,3)

tbl being the table that has your orginal data in.

But beware ... each time you run the code you will drop the first two characters from your field !!

Assuming all your IDNum start with TX, then you could try -

UPDATE tbl SET IDNum = Mid(IDNum,3)
WHERE LEFT(IDNum,2) = 'TX'

and run that to your hearts content.

Why don't you strip the TX out before it even hits your table ??

Hope this helps