Add zeros and remove hyphens

[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

 

[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

 

[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

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

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

 

[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!!!!

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

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