Calculation

  • Thread starter Thread starter BeccySmith
  • Start date Start date
B

BeccySmith

I have a table [Table1], within that table I have a field
[Field1] which holds a ten digit number. The table is
already populated with data and is reguarly updated by
importing data into it.

I would like to have a validation check on the data within
Field1. The data in Field1 is made up of ten digits. The
last digit is a check digit based. The caluation for this
digit is as follows:

Step 1 - multiply each of the first nine digits by a
weighting factor as follows:

Digit Position (starting from Left) Factor
1 10
2 9
3 8
4 7
5 6
6 5
7 4
8 3
9 2

Step 2 - add the results of each multiplication together
Step 3- Divide the total by 11 and establish the remainder
Step 4 - subtract the remainder from 11 to give the check
digit.


How do I put this into a calculation within Access so that
when the result of the calculation do not match the
expected check digit that an error is thrown up?
 
Beccy

Write a function that returns either a True/False value or a check digit
(your choice, depending on how you want to handle it).

Open a new code module and:
1. include the input (your ten digit number) parameter and the output
format (see above)
2. parse apart the digits, using something like the Mid() string
handling function (you could use10 variables, or just a couple and
"accumulate" as you cycle through)
3. carry out the math
4. generate (and or verify) the check digit

Good luck

Jeff Boyce
<Access MVP>
 
Back
Top