strip the letter off of a code

[nickrugado]

I have many records, all of them are at least 10 spaces some are 11 or 12 ... The 11th and 12th spaces are letters.

I want to strip off the numbers and the letters and import them into seperate fields.

An example of the data... parsing left 10 to a field and right blank to 2 charachters to another field.

0000-02125
0000-00510
0000-00510N
0000-00210BV

Any help would be appreciated.
Thanks

 

[SgtJarrow]

Left([fieldname], 10) would grab the first ten characters.
Right([fieldname], 2) would grab the last two...but this may sometimes be numbers in your case.

You should look at the Mid function

Mid([fieldname], 11)

Mid starts at a specified character and gives you everything after that. There is an optional parameter that tells it to only get so many characters, but in your case, you don't need it.

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB/Access Programmer

 

[JerryKlmns]

How about this?

SELECT Left(yourField,10) As NewField1,
IIF(LEN(yourField)>10, Right(yourField,LEN(yourField)-10),"")As NewField2
FROM yourTable;