Need to add hyphens

[Ellen]

Hello,
There are ID number to identify chemicals called CAS
Registry numbers. They are either expressed as a nine
digit number or as a number with six or less numbers
followed by a hyphen, then two numbers followed by a
hyphen, then a single digit numbers.

Examples would be:

010213782 equivalent to 10213-78-2 or,
000514103 equivalent to 514-10-3 or,
203742976 equivalent to 203742-97-6

The same idea as a SSN, except leading zeros are dropped.

I have a table full of these nine digit numbers I'd like
converted to the hyphenated version. Anyone have any
ideas?

Thank you in advance.

Ellen

 

[Brian Camire]

I think an expression like this should work:

Format([Your Nine-Digit Number Field], "0-00-0")

 

[Brian Camire]

Yes, but you could use it in an update query, like:

UPDATE
[Your Table]
SET
[Your Table].[Your New Hyphenated Field] = Format([Your Table].[Your
Nine-Digit Number Field], "0-00-0")

if you wanted to make the change permanently.

 

[Guest]

Thanks, Bernd. I wasn't quite sure how to use your
expression, but I modified your code to break up the
number into three fields, the first one being a number so
the leading zeros would be dropped. The second two were
text as all characters, including zeros were necessary.
From there I concatenated these three fields, placing the
hyphens in between the three fields.

Bye!

-----Original Message-----
Hi Ellen,

You could run either an Update Query or a Make Table Query.
What ever you want to do, the following fomula would

change your ID number to what you would like to have

a = ID number (presuming it is a 9 digit long Text field)
Test = New fields name

Test: Left([a],6) & '-' & Mid([a],7,2) & '-' & Right ([a],1)

You can put this fomula in a query.

Regards
Bernd
.