validation rule for a unique identifier number?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to create a validation rule to validate that an identifcation number I
put into a table is actually a CAS RN (chemical substance id number).

CAS #'s can be up to nine digits in the form NNNNNN-XX-C.

The first six N's can be any number, with any of the digits omitted. Only
one digit is required for the X's, but two can be present. The C represents
the check digit.

To calculate a checksum for a CAS Number:

starting at the second-last digit and progressing to the left, number each
digit from 1 to N, Then, multiply each digit by the number you assigned. Add
the sums, and take the last digit of that sum, this digit should match the
last digit (C) of the CAS number.

I've been having problems figuring this one out. The problems that I see
are that the CAS can be any where from 3 to 9 digits long, so a simple imput
mask doesn't hlep much. Maybe you have the answer!
 
On Tue, 12 Oct 2004 12:45:03 -0700, Hans Hilbert <Hans
I need to create a validation rule to validate that an identifcation number I
put into a table is actually a CAS RN (chemical substance id number).

CAS #'s can be up to nine digits in the form NNNNNN-XX-C.

The first six N's can be any number, with any of the digits omitted. Only
one digit is required for the X's, but two can be present. The C represents
the check digit.
Click to expand...

Um? Not in my recollection. Leading zeros can be ommitted in some
displays, but water is still RN 000000011.
To calculate a checksum for a CAS Number:

starting at the second-last digit and progressing to the left, number each
digit from 1 to N, Then, multiply each digit by the number you assigned. Add
the sums, and take the last digit of that sum, this digit should match the
last digit (C) of the CAS number.

I've been having problems figuring this one out. The problems that I see
are that the CAS can be any where from 3 to 9 digits long, so a simple imput
mask doesn't hlep much. Maybe you have the answer!
Click to expand...

Use an Input Mask of 000000000 and force entry of the leading zeros.
Or, use an expression like

Dim RN As String
RN = Right(Me!CASRN & "000000000", 9)

before parsing out the digits.

Oh heck... I should be able to do this:

Public Function ValidRN(vRN As Variant) As Boolean
Dim RN As String
Dim iPos As Integer
Dim iChk As Integer
RN = Right(vRN & "000000000", 9)
For iPos = 1 to 8
iChk = iPos * CInt(Mid([RN], 9 - iPos, 1)
Next iPos
iChk = iChk Mod 10
ValidRN = (iChk = Cint(Right([RN], 1))
End Function


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps



John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
You will need to write your own validation routine for this one. I'm
assuming that all you know for sure is that the last digit has to match the
check digit processing. Beyond that, if there are no dashes present, you
have no idea if there are two Xs.

You can either break the input up into three fields, or force the hyphens.
From there, it's a matter of parsing and looping to develop the check digit.
 
Back
Top