Add zeros and remove hyphens

E

Ellen

Hello,
I have a field of CAS# numbers, which identify chemicals. These numbers can
exist either as a nine digit number or a number with hyphens between the
first and second right hand digits and between the third and fourth right
hand digits. In the second version the leading zeros are dropped.

For instance,

10213-78-2 to become 010213782
17201-15-9 to become 017201159
126-00-1 to become 000126001
977139-89-1 to become 977139891

I have the version that has hyphens and I'd like to convert them to the nine
digit version without hyphens. Can someone help me?

Thank you in advance.

Ellen
 
R

Roger Carlson

If you want to do an entire table, you can use an Update query:

UPDATE CASTable SET CASTable.CAS =
Format(Replace([CAS],"-",""),"000000000");

The Replace function will replace the hyphen with an empty string, and the
Format function will add the leading zeros.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
E

Ellen

Thanks for your help, Roger. Actually I wasn't clear with what I wanted to
do. I'd like to keep the hyphenated numbers and update a new field
containing the numbers with the leading zeros. Could you help me?

Thank you in advance,
Ellen

Roger Carlson said:
If you want to do an entire table, you can use an Update query:

UPDATE CASTable SET CASTable.CAS =
Format(Replace([CAS],"-",""),"000000000");

The Replace function will replace the hyphen with an empty string, and the
Format function will add the leading zeros.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Ellen said:
Hello,
I have a field of CAS# numbers, which identify chemicals. These numbers can
exist either as a nine digit number or a number with hyphens between the
first and second right hand digits and between the third and fourth right
hand digits. In the second version the leading zeros are dropped.

For instance,

10213-78-2 to become 010213782
17201-15-9 to become 017201159
126-00-1 to become 000126001
977139-89-1 to become 977139891

I have the version that has hyphens and I'd like to convert them to the nine
digit version without hyphens. Can someone help me?

Thank you in advance.

Ellen
 
E

Ellen

Thanks, Roger. Your code worked beautifully! I used it as such:
UPDATE CEDI SET CEDI.[CAS REG NO] = Format(Replace([CAS],"-,""),"000000000")
WHERE (((CEDI.[CAS REG NO]) Is Null));

Please ignore my earlier post.

Merry Christmas to you!!!!


Roger Carlson said:
If you want to do an entire table, you can use an Update query:

UPDATE CASTable SET CASTable.CAS =
Format(Replace([CAS],"-",""),"000000000");

The Replace function will replace the hyphen with an empty string, and the
Format function will add the leading zeros.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L



Ellen said:
Hello,
I have a field of CAS# numbers, which identify chemicals. These numbers can
exist either as a nine digit number or a number with hyphens between the
first and second right hand digits and between the third and fourth right
hand digits. In the second version the leading zeros are dropped.

For instance,

10213-78-2 to become 010213782
17201-15-9 to become 017201159
126-00-1 to become 000126001
977139-89-1 to become 977139891

I have the version that has hyphens and I'd like to convert them to the nine
digit version without hyphens. Can someone help me?

Thank you in advance.

Ellen
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top