Check digits

[DrONDeN]

I'm trying to implement a Mod 11 data validation into ms access with a
custom weighting. The weighting is as follows:

Position: 9 8 7 6 5 4 3
2 1
Weighting: 6 3 7 9 10 5 8 4
2

Therefore the check digit for number 550015209 would be worked out as
follows:

1. Multiply each number by it's weighing according to position

Weighting: 6 3 7 9 10 5 8 4
2
number: 5 5 0 0 1 5 2
0 9

= 30 15 0 0 10 25 16 0
18

2. Add the products of step 1 together and minus 1

= 114 - 1 = 113

3. Mod 11 the product of step 2

= 113 mod 11 = 3

4. Subtract the product of step 3 from 11

= 11 - 3

5. Check digit = 8 Therefore the new number would be 5500152098

If the check digit = 10 then this is replaced by a hypen eg 552081815-

I would like to implement it so that when the reference number is
entered into a control bound to the reference number field, validation
is performed which checks that the number is valid using method above.
If not, then a message box is displayed warning of error and the text
box is cleared ready for a second attempt.

Is this possible using a query or would I have to use VB. Whichever
way, I would be enourmously grateful of any help pointing me in the
right direction on this one.

 

[Dale Fye]

It's probably just my ignorance of the subject matter, but what are you
checking this value against?

You could write a user defined function to determine the checkdigit value,
something like the following, that you could then use in a query

Private Function CheckDigit(strValue as string) as integer

Dim intLoop as integer, lngValue as long
Dim strWeights(10 ) as string
strWeights = split("6,3,7,9,10,5,8,4,2")

if LEN(strValue) <> 9 then
CheckDigit = -1
Exit Function
endif

'Step 1
lngValue = 0
For intLoop = 1 to 9
lngValue = lngValue + val(mid(strValue, intLoop,1)) *
Val(strWeights(intLoop))
Next

'Step 2
lngValue = lngValue - 1
'Steps 3 & 4
CheckDigit = 11 - (lngValue mod 11)

End Function

 

[Bob Quintal]

I'm trying to implement a Mod 11 data validation into ms
access with a custom weighting. The weighting is as follows:

Position: 9 8 7 6 5 4 3
2 1
Weighting: 6 3 7 9 10 5 8
4 2

Therefore the check digit for number 550015209 would be worked
out as follows:

1. Multiply each number by it's weighing according to position

Weighting: 6 3 7 9 10 5 8
4 2
number: 5 5 0 0 1 5 2
0 9

= 30 15 0 0 10 25 16
0 18

2. Add the products of step 1 together and minus 1

= 114 - 1 = 113

3. Mod 11 the product of step 2

= 113 mod 11 = 3

4. Subtract the product of step 3 from 11

= 11 - 3

5. Check digit = 8 Therefore the new number would be
5500152098

If the check digit = 10 then this is replaced by a hypen eg
552081815-

I would like to implement it so that when the reference number
is entered into a control bound to the reference number field,
validation is performed which checks that the number is valid
using method above. If not, then a message box is displayed
warning of error and the text box is cleared ready for a
second attempt.

Is this possible using a query or would I have to use VB.
Whichever way, I would be enourmously grateful of any help
pointing me in the right direction on this one.

I strongly doubt that this would be doable in a query, so write
a vb function.

The validation code would go into the control's before update
event.

The number must be in a text type field.
I assume that if you enter the 9 digits, you will want to add
the checkdigit, and if it's 10 digits, validate the 10th against
the first 9?

a select case within a for-next loop would probably be the way
to go, accumulating the sum in a variable

This is incomplete and undebugged, but should point you in hte
correct direction.


accum = 0
if len(trim(MyNumber)) = 9 OR _
len(trim(MyNumber)) = 10 then
for pos = 1 to 9
select case mid(MyNumber,1,1)
case 9
weight = 6
case ...' fill these in.
case 1
weight = 2
end select
accum = accum + (val(mid(number,1,1)) * weight)
next pos
checksum = 11 - ((accum - 1) mod 11)
if checksum = 10 then checksum = "-"
if len(myNumber) = 9 then
MyNumber = mynumber & checksum
elseif mid(MyNumber,10,1) = checksum then
MyNumber = "Valid"
else
MyNumber = "Bad Checksum"
end if
end if

 

[DrONDeN]

I strongly doubt that this would be doable in a query, so write
a vb function.

The validation code would go into the control's before update
event.

The number must be in a text type field.
I assume that if you enter the 9 digits, you will want to add
the checkdigit, and if it's 10 digits, validate the 10th against
the first 9?

a select case within a for-next loop would probably be the way
to go, accumulating the sum in a variable

This is incomplete and undebugged, but should point you in hte
correct direction.

accum = 0
if len(trim(MyNumber)) = 9 OR _
len(trim(MyNumber)) = 10 then
for pos = 1 to 9
select case mid(MyNumber,1,1)
case 9
weight = 6
case ...' fill these in.
case 1
weight = 2
end select
accum = accum + (val(mid(number,1,1)) * weight)
next pos
checksum = 11 - ((accum - 1) mod 11)
if checksum = 10 then checksum = "-"
if len(myNumber) = 9 then
MyNumber = mynumber & checksum
elseif mid(MyNumber,10,1) = checksum then
MyNumber = "Valid"
else
MyNumber = "Bad Checksum"
end if
end if

Thanks Bob!

Dale, the idea is to use the procedure to validate numbers as they are
typed in. So if an error is made on entering the number, the procedure
will let the user know and ask user to double check the number they
have entered.

 

[Jamie Collins]

I strongly doubt that this would be doable in a query

You suspect wrong e.g.

SELECT '5500152098' AS test_value,
IIF(
11 - (((CLNG(MID(test_value, 1, 1)) * 6)
+ (CLNG(MID(test_value, 2, 1)) * 3)
+ (CLNG(MID(test_value, 3, 1)) * 7)
+ (CLNG(MID(test_value, 4, 1)) * 9)
+ (CLNG(MID(test_value, 5, 1)) * 10)
+ (CLNG(MID(test_value, 6, 1)) * 5)
+ (CLNG(MID(test_value, 7, 1)) * 8)
+ (CLNG(MID(test_value, 8, 1)) * 4)
+ (CLNG(MID(test_value, 9, 1)) * 2)
- 1) MOD 11) = 10, '-',
CSTR(11 - (((CLNG(MID(test_value, 1, 1)) * 6)
+ (CLNG(MID(test_value, 2, 1)) * 3)
+ (CLNG(MID(test_value, 3, 1)) * 7)
+ (CLNG(MID(test_value, 4, 1)) * 9)
+ (CLNG(MID(test_value, 5, 1)) * 10)
+ (CLNG(MID(test_value, 6, 1)) * 5)
+ (CLNG(MID(test_value, 7, 1)) * 8)
+ (CLNG(MID(test_value, 8, 1)) * 4)
+ (CLNG(MID(test_value, 9, 1)) * 2)
- 1) MOD 11))) AS check_digit

The validation code would go into the control's before update
event.

Being able to achieve this using SQL code is important because the OP
will want to use this is a validation rule at the engine level to
prevent values with an incorrect check digit entering the database
regardless of source e.g. (ANSI-92 Query Mode):

CREATE TABLE Employees
(
employee_number CHAR(10) NOT NULL PRIMARY KEY,
CONSTRAINT employee_number__pattern
CHECK
(
employee_number LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]
[0-9][0-9-]'
),
CONSTRAINT employee_number__check_digit
CHECK
(
MID(employee_number, 10, 1) =
IIF(
11 - (((CLNG(MID(employee_number, 1, 1)) * 6)
+ (CLNG(MID(employee_number, 2, 1)) * 3)
+ (CLNG(MID(employee_number, 3, 1)) * 7)
+ (CLNG(MID(employee_number, 4, 1)) * 9)
+ (CLNG(MID(employee_number, 5, 1)) * 10)
+ (CLNG(MID(employee_number, 6, 1)) * 5)
+ (CLNG(MID(employee_number, 7, 1)) * 8)
+ (CLNG(MID(employee_number, 8, 1)) * 4)
+ (CLNG(MID(employee_number, 9, 1)) * 2)
- 1) MOD 11) = 10, '-',
CSTR(11 - (((CLNG(MID(employee_number, 1, 1)) * 6)
+ (CLNG(MID(employee_number, 2, 1)) * 3)
+ (CLNG(MID(employee_number, 3, 1)) * 7)
+ (CLNG(MID(employee_number, 4, 1)) * 9)
+ (CLNG(MID(employee_number, 5, 1)) * 10)
+ (CLNG(MID(employee_number, 6, 1)) * 5)
+ (CLNG(MID(employee_number, 7, 1)) * 8)
+ (CLNG(MID(employee_number, 8, 1)) * 4)
+ (CLNG(MID(employee_number, 9, 1)) * 2)
- 1) MOD 11)))
)
)
;

INSERT INTO Employees (employee_number) VALUES ('5500152098')
;
INSERT INTO Employees (employee_number) VALUES ('552081815-')
;
INSERT INTO Employees (employee_number) VALUES ('A52081815-') -- fails
pattern rule
;
INSERT INTO Employees (employee_number) VALUES ('5520818159') -- fails
check digit rule
;

Jamie.

--

 

[Bob Quintal]

Wow, thank you for teaching me something useful.

Q.

I strongly doubt that this would be doable in a query

You suspect wrong e.g.

SELECT '5500152098' AS test_value,
IIF(
11 - (((CLNG(MID(test_value, 1, 1)) * 6)
+ (CLNG(MID(test_value, 2, 1)) * 3)
+ (CLNG(MID(test_value, 3, 1)) * 7)
+ (CLNG(MID(test_value, 4, 1)) * 9)
+ (CLNG(MID(test_value, 5, 1)) * 10)
+ (CLNG(MID(test_value, 6, 1)) * 5)
+ (CLNG(MID(test_value, 7, 1)) * 8)
+ (CLNG(MID(test_value, 8, 1)) * 4)
+ (CLNG(MID(test_value, 9, 1)) * 2)
- 1) MOD 11) = 10, '-',
CSTR(11 - (((CLNG(MID(test_value, 1, 1)) * 6)
+ (CLNG(MID(test_value, 2, 1)) * 3)
+ (CLNG(MID(test_value, 3, 1)) * 7)
+ (CLNG(MID(test_value, 4, 1)) * 9)
+ (CLNG(MID(test_value, 5, 1)) * 10)
+ (CLNG(MID(test_value, 6, 1)) * 5)
+ (CLNG(MID(test_value, 7, 1)) * 8)
+ (CLNG(MID(test_value, 8, 1)) * 4)
+ (CLNG(MID(test_value, 9, 1)) * 2)
- 1) MOD 11))) AS check_digit

The validation code would go into the control's before update
event.

Being able to achieve this using SQL code is important because
the OP will want to use this is a validation rule at the
engine level to prevent values with an incorrect check digit
entering the database regardless of source e.g. (ANSI-92 Query
Mode):

CREATE TABLE Employees
(
employee_number CHAR(10) NOT NULL PRIMARY KEY,
CONSTRAINT employee_number__pattern
CHECK
(
employee_number LIKE
'[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]
[0-9][0-9-]'
),
CONSTRAINT employee_number__check_digit
CHECK
(
MID(employee_number, 10, 1) =
IIF(
11 - (((CLNG(MID(employee_number, 1, 1)) * 6)
+ (CLNG(MID(employee_number, 2, 1)) * 3)
+ (CLNG(MID(employee_number, 3, 1)) * 7)
+ (CLNG(MID(employee_number, 4, 1)) * 9)
+ (CLNG(MID(employee_number, 5, 1)) * 10)
+ (CLNG(MID(employee_number, 6, 1)) * 5)
+ (CLNG(MID(employee_number, 7, 1)) * 8)
+ (CLNG(MID(employee_number, 8, 1)) * 4)
+ (CLNG(MID(employee_number, 9, 1)) * 2)
- 1) MOD 11) = 10, '-',
CSTR(11 - (((CLNG(MID(employee_number, 1, 1)) * 6)
+ (CLNG(MID(employee_number, 2, 1)) * 3)
+ (CLNG(MID(employee_number, 3, 1)) * 7)
+ (CLNG(MID(employee_number, 4, 1)) * 9)
+ (CLNG(MID(employee_number, 5, 1)) * 10)
+ (CLNG(MID(employee_number, 6, 1)) * 5)
+ (CLNG(MID(employee_number, 7, 1)) * 8)
+ (CLNG(MID(employee_number, 8, 1)) * 4)
+ (CLNG(MID(employee_number, 9, 1)) * 2)
- 1) MOD 11)))
)
)
;

INSERT INTO Employees (employee_number) VALUES ('5500152098')
;
INSERT INTO Employees (employee_number) VALUES ('552081815-')
;
INSERT INTO Employees (employee_number) VALUES ('A52081815-')
-- fails pattern rule
;
INSERT INTO Employees (employee_number) VALUES ('5520818159')
-- fails check digit rule
;

Jamie.