[AccessD] adding control number

Hello Gustav,

i used the function below in a query,
<sql>
SELECT Right(ModulusAppend(PatientID,10),Len(PatientID)) AS Expr1
FROM tblPatientgegevens;
</sql>

but the result wasn't correct.

In the list of about 3000 PatientID's i checked three ID's by hand, but the results didn't match with the results from the function.

00100499  (ID)
01004993  (function)    
01004994  (check by hand)


00100905  (ID)
01009059  (function)    
01009056  (check by hand)


00111843  (ID)
01118439  (function)    
01118434  (check by hand)


Is it possible for you to check the function?

Thanks 

Pedro Janssen


----- Original Message -----
From: "Gustav Brock" <Gustav at cactus.dk>
To: <accessd at databaseadvisors.com>
Sent: Friday, August 26, 2005 1:40 PM
Subject: Re: [AccessD] adding control number


> Hi Pedro 
> 
> Looks like a Modulus 10 check digit calculation.
> Below is a function that will do this for you in a maintainable way.
> 
> Use it like this:
> 
> strChk = Right(ModulusAppend("00327833", 10), Len("00327833"))
> 
> /gustav
> 
> <code>
> 
> Function ModulusAppend( _
>   ByVal strNumber As String, _
>   ByVal intModulus As Integer) _
>   As String
> 
> ' Appends a Modulus 10 or 11 check digit to strNumber.
> '
> ' 1999-10-08. Cactus Data ApS, CPH.
> 
>   Dim intC As Integer, intF As Integer, intN As Integer
>   Dim intL As Integer, intM As Integer, intT As Integer
>   Dim strNumCheck As String
>   Dim strNumChr   As String
>   Dim strNumClean As String
>   
>   ' Max. length of number.
>   intM = 32 - 1
>   
>   If intModulus = 10 Or intModulus = 11 Then
>     intL = Len(strNumber)
>     ' Remove non-digits.
>     For intN = 0 To intL - 1
>       strNumChr = Mid(strNumber, intN + 1, 1)
>       If (Asc(strNumChr) >= 48) And (Asc(strNumChr) <= 57) Then
>         strNumClean = strNumClean & strNumChr
>       End If
>     Next intN
>     strNumber = strNumClean
>     intL = Len(strNumber)
>   End If
>   
>   If intL > 0 And intL <= intM Then
>     For intN = 0 To intL - 1
>       intC = Val(Mid(strNumber, intL - intN, 1))
>       Select Case intModulus
>         Case Is = 10
>           intF = (1 + ((intN + 1) Mod 2))
>           intC = intF * intC
>           intC = Int(intC / 10) + (intC Mod 10)
>         Case Is = 11
>           intF = 2 + (intN Mod 6)
>           intC = intF * intC
>       End Select
>       intT = intT + intC
>     Next
>     Select Case intModulus
>       Case Is = 10
>         intC = intT - (intT Mod intModulus) + intModulus
>         strNumCheck = Format((intC - intT) Mod intModulus, "@")
>       Case Is = 11
>         intC = intModulus - (intT Mod intModulus)
>         Select Case intC
>           Case Is = 11
>             ' A check digit for this number cannot be calculated!
>             strNumber = vbNullString
>             intC = 0
>             Beep
>           Case Is = 10
>             intC = 0
>         End Select
>         strNumCheck = Format(intC, "@")
>     End Select
>     strNumber = strNumber & strNumCheck
>   Else
>     strNumber = "0"
>   End If
>   
>   ModulusAppend = strNumber
> 
> End Function
> 
> </code>
> 
> 
> >>> pedro at plex.nl 26-08-2005 11:11 >>>
> Hello Group,
> 
> I have an ID (text, because it begins with 00) that exist of 8
> numbers.
> I need to change this ID by removing the first 0, and by adding a extra
> control number.
> This control number can be calculated from this ID. This can be done by
> several query's. Is it possible to do in one query??
> 
> For example.
> 
> I have ID:  00327833
> 
> Take the number of the odd positions:    0 3 7 3
> 
> Place these numbers behind each other to make one number:    0373
> 
> Multiply this number with 2:    0373 x 2 = 0746
> 
> Count up the separate numbers from this last multiplication:    0 + 7 +
> 4 + 6 = 17
> 
> >From the original ID, take the even positions:    0 2 8 3
> 
> Count up the separate numbers from the even positions with the sum of
> the last count up:    17 + 0 + 2 + 8 + 3 = 30
> 
> The last number of the previous sum is the extra control number:    0
> 
> >From the original ID, remove the first 0 and add the control number:  
>  03278330
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
>