Making a control number based on a value

[snekkis]

Hi,

I'm making a OCR customer control number and the Bank has given the way to calculate it.

Based on a couple of fields, I get a value of 10 number. In all, it should be 11, where the last is a control number based upon the value of the 10 numbers. I don't mean that someone should figure it all out. I pretty much understand the basic, except for the function I need help for. And that is to separate all the 10 numbers individually, because they need to be added together.

I tried to use the Right(field,1), Right(field,2), Right(field.3), Right(field,4)... formula, but that gave all the numbers from 1 and upwards.

How can I separate the numbers individually?

I can fill in on how this example calculation should be:

FieldValue=1234567890

I must multiply each individual number with alternate number 1 and 2, starting with 1 from right. 0*1,9*2,8*1,7*2,6*1,5*2,4*1,3*2,2*1,1*2 Here we get:

0, 18, 8, 14, 6, 10, 4, 6, 2, 2 Now, this should be added together, but like this:

0+1+8+8+1+4+6+1+0+4+6+2+2=43

The first number of 43, number 3 should be subtracted from 10, always 10. 10-3=7

The control number is then 7. It should be added to the fieldvalue. ControlValue = 12345678903

Just added if someone should ask why I need it so.

[fishma]

You have explained your example wrong... But I made a few assumptions...

Two fields

FieldValue = text

FieldValue_and_control = calculation - result = text

The calculation is as follows...

FieldValue &

(10 -

Right(

(Case(

((Middle( FieldValue, 10, 1)) * 1) > 9, Right(((Middle( FieldValue, 10, 1)) * 1), 1) + Left(((Middle( FieldValue, 10, 1)) * 1), 1), ((Middle( FieldValue, 10, 1)) * 1)

) +

Case(

((Middle( FieldValue, 9, 1)) * 2) > 9, Right(((Middle( FieldValue, 9, 1)) * 2), 1) + Left(((Middle( FieldValue, 9, 1)) * 2), 1), ((Middle( FieldValue, 9, 1)) * 2)

) +

Case(

((Middle( FieldValue, 8, 1)) * 1) > 9, Right(((Middle( FieldValue, 8, 1)) * 1), 1) + Left(((Middle( FieldValue, 8, 1)) * 1), 1), ((Middle( FieldValue, 8, 1)) * 1)

) +

Case(

((Middle( FieldValue, 7, 1)) * 2) > 9, Right(((Middle( FieldValue, 7, 1)) * 2), 1) + Left(((Middle( FieldValue, 7, 1)) * 2), 1), ((Middle( FieldValue, 7, 1)) * 2)

) + Case(

((Middle( FieldValue, 6, 1)) * 1) > 9, Right(((Middle( FieldValue, 6, 1)) * 1), 1) + Left(((Middle( FieldValue, 6, 1)) * 1), 1), ((Middle( FieldValue, 6, 1)) * 1)

) +

Case(

((Middle( FieldValue, 5, 1)) * 2) > 9, Right(((Middle( FieldValue, 5, 1)) * 2), 1) + Left(((Middle( FieldValue, 5, 1)) * 2), 1), ((Middle( FieldValue, 5, 1)) * 2)

) + Case(

((Middle( FieldValue, 4, 1)) * 1) > 9, Right(((Middle( FieldValue, 4, 1)) * 1), 1) + Left(((Middle( FieldValue, 4, 1)) * 1), 1), ((Middle( FieldValue, 4, 1)) * 1)

) +

Case(

((Middle( FieldValue, 3, 1)) * 2) > 9, Right(((Middle( FieldValue, 3, 1)) * 2), 1) + Left(((Middle( FieldValue, 3, 1)) * 2), 1), ((Middle( FieldValue, 3, 1)) * 2)

) + Case(

((Middle( FieldValue, 2, 1)) * 1) > 9, Right(((Middle( FieldValue, 2, 1)) * 1), 1) + Left(((Middle( FieldValue, 2, 1)) * 1), 1), ((Middle( FieldValue, 2, 1)) * 1)

) +

Case(

((Middle( FieldValue, 1, 1)) * 2) > 9, Right(((Middle( FieldValue, 1, 1)) * 2), 1) + Left(((Middle( FieldValue, 1, 1)) * 2), 1), ((Middle( FieldValue, 1, 1)) * 2)

))

,1))

...enjoy...

[BuddySystems]

ok, so the calc would be:

fieldvalue &

Right(((right,1)*(left,1))+(middle(9,1)*middle(2,1))+(middle(8,1)*middle(1,1))+(middle(7,1)*middle(2,1))+(middle(6,1)*middle(1,1))+(middle(5,1)*middle(2,1))+(middle(4,1)*middle(1,1))+(middle(3,1)*middle(2,1))+(middle(2,1)*middle(1,1))+(middle(1,1)*middle(2,1))

,1)-10

[snekkis]

Sorry, I made to write control number 3 at the back instead of 7. It should be Controllvalue=12345678907 of course. And that is what I get from the first formula.

Thanks for the response, guys.