Conversion of chemical ID numbers

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

I can readily remove all the hyphens. I only then need to
add leading zeros so that they're nine digits long.

Thank you in advance.

Ellen

 

[Roger Carlson]

Well, assuming you have already removed the hyphens, you can add leading
zeros with a simple Update query:

UPDATE MyTable SET CAS = Format([CAS],"000000000");

The brackets around CAS in the format statement are VERY important if you
create this in the query builder. I would also suggest making a back-up of
the table before running it, just in case.