Need to add hyphens

  • Thread starter Thread starter Ellen
  • Start date Start date
E

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
 
I think an expression like this should work:

Format([Your Nine-Digit Number Field], "0-00-0")
 
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.
 
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
Click to expand...
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
.
Click to expand...
 
Back
Top