function to validate number entry in table (1 Viewer)

[k-otic]

can someone help me with a question?
i need to validate a number entry in a table...
i don't know hhow to write this function

function needs to validate

number needs to have 9 digits;
firt digit has to be 1, 2, 5, 6, 8, or 9
control sum given by 9xn1 +8xn2+ 7xn3+ 6xn4+ 5xn5 +4xn6 +3xn7 +2xn8 +n9 has to be multiple of 11 (sum%11=0).
n1 is the first digit, n9 is the last digit (check digit).
someone has a function that does this?

 

[JANR]

Did you search this site for VIN NUMBER?

Here is one: http://www.access-programmers.co.uk/forums/showthread.php?t=150955&highlight=VIN+number

JR

 

[DCrake]

This is a similar function that validates a NHS number, I am sure you can modify it to suit your needs.

Public Function ValidateNHSNumber(NHSNumber As Variant) As Boolean
    '
    ' Modulus 11  Check Digit Algorithm
    ' Validates NHS Number

    Dim fValid As Boolean, intResult As Integer, intCheckDigit As Integer
On Error GoTo Err_Handler
    
    fValid = True

    If Not IsNull(NHSNumber) Then
        Select Case NHSNumber
            Case "1111111111", "2222222222", "3333333333", "4444444444", "5555555555", "6666666666", "7777777777", "8888888888", "9999999999", "0000000000"
                fValid = False
            Case Else
            
                intResult = 0
                'Perform Check Digit Calculation
                intResult = Left(NHSNumber, 1) * 10
                intResult = intResult + Mid(NHSNumber, 2, 1) * 9
                intResult = intResult + Mid(NHSNumber, 3, 1) * 8
                intResult = intResult + Mid(NHSNumber, 4, 1) * 7
                intResult = intResult + Mid(NHSNumber, 5, 1) * 6
                intResult = intResult + Mid(NHSNumber, 6, 1) * 5
                intResult = intResult + Mid(NHSNumber, 7, 1) * 4
                intResult = intResult + Mid(NHSNumber, 8, 1) * 3
                intResult = intResult + Mid(NHSNumber, 9, 1) * 2
                intCheckDigit = Right(NHSNumber, 1)
                intResult = intResult Mod 11
                intResult = 11 - intResult
                
                If intResult = 11 Then
                    intResult = 0
                End If
                
                If intResult = 10 Or (intResult <> intCheckDigit) Then
                    fValid = False
                End If
            End Select
            
            ValidateNHSNumber = fValid
            
    End If
    
Exit_Handler:
    Exit Function
    
Err_Handler:
    MsgBox Err & " " & Err.Description
    Resume Exit_Handler:
End Function

David

 

[k-otic]

Did you search this site for VIN NUMBER?

Here is one: http://www.access-programmers.co.uk/forums/showthread.php?t=150955&highlight=VIN+number

JR

how could i know what is a vin number?? i'm portuguese, i need this to validade a portuguese number of identification, so i couldn't search for VIN number....

Dear DCrake, the problem is that i don't understant nothing about that code, so edit for me is very difficult...

 

[JANR]

how could i know what is a vin number?? i'm portuguese, i need this to validade a portuguese number of identification, so i couldn't search for VIN number....

It was the first thought that crossed my mind when I saw the sequence. And the post i found deals with arrays so with some modification it might lead you to a workable solution, the same goes for DCracke's code.

JR

 

[JANR]

function needs to validate
number needs to have 9 digits;

To validate number of digits use the Len function aka If Len(Variabel) = 9 Then True ELSE False.

first digit has to be 1, 2, 5, 6, 8, or 9

Use a SELECT Case statement here, First declare an integer which holds the value of the first number in your variabel.
To find the number you can use the Mid function like: x = Mid(Variabel, 1, 1)

Dim x as Integer
x = Mid(YourVariabel, 1, 1)
   SELECT CASE x
 CASE 1, 2, 5, 6, 8, 9
 'What to do if TRUE
   
 CASE ELSE   
 'What to do if FALSE
   END SELECT

control sum given by 9xn1 +8xn2+ 7xn3+ 6xn4+ 5xn5 +4xn6 +3xn7 +2xn8 +n9 has to be multiple of 11 (sum%11=0).
n1 is the first digit, n9 is the last digit (check digit).

When you state that for the calculation to be true SUM/11 = 0, then (9xn1 +8xn2+ 7xn3+ 6xn4+ 5xn5 +4xn6 +3xn7 +2xn8 +n9) has to
equal zero, so the 5. and 9. number cannot be other than zero.

The calculation can probebly be done much more elegant but here is one way:

Public Function calcNum(z As Variant) As Boolean
Dim sSum As Currency
sSum = 0
sSum = sSum + Mid(z, 9, 1) * Mid(z, 1, 1)
sSum = sSum + Mid(z, 8, 1) * Mid(z, 2, 1)
sSum = sSum + Mid(z, 7, 1) * Mid(z, 3, 1)
sSum = sSum + Mid(z, 6, 1) * Mid(z, 4, 1)
sSum = sSum + Mid(z, 5, 1) * Mid(z, 5, 1)
sSum = sSum + Mid(z, 4, 1) * Mid(z, 6, 1)
sSum = sSum + Mid(z, 3, 1) * Mid(z, 7, 1)
sSum = sSum + Mid(z, 2, 1) * Mid(z, 8, 1)
sSum = sSum + Mid(z, 9, 1)
sSum = sSum / 11
    If sSum <> 0 Then
        calcNum = False
    Else
        calcNum = True
    End If

End Function

If I haven't missed the point then here is one solution:

Option Compare Database
Option Explicit

Public Function sNumber(gNum As Variant) As Boolean
Dim fArg As Boolean, newArray As Variant, x As Integer
fArg = False
    If Len(gNum) = 9 Then  ' correct number of digits, not tested for alpha
            newArray = gNum  ' Pass on 1. test
        Else
            fArg = False  'Fail on 1.test and will fail on second
            Exit Function
    End If
    
    x = Mid(newArray, 1, 1)  ' find first number to evaluate in select statement
    
    Select Case x
        Case 1, 2, 5, 6, 8, 9   ' To Pass on 2. test X = one of these numbers
                        
            If (calcNum(newArray) = False) Then
                    fArg = False  ' Fail on final test
                Else
                    fArg = True ' Pass on final test
            End If
        
        Case Else
            fArg = False   ' Fails on 2. test
        
    End Select
    
sNumber = fArg      ' send true/false back to calling sub
End Function

Public Function calcNum(z As Variant) As Boolean
Dim sSum As Currency
sSum = 0
sSum = sSum + Mid(z, 9, 1) * Mid(z, 1, 1)
sSum = sSum + Mid(z, 8, 1) * Mid(z, 2, 1)
sSum = sSum + Mid(z, 7, 1) * Mid(z, 3, 1)
sSum = sSum + Mid(z, 6, 1) * Mid(z, 4, 1)
sSum = sSum + Mid(z, 5, 1) * Mid(z, 5, 1)
sSum = sSum + Mid(z, 4, 1) * Mid(z, 6, 1)
sSum = sSum + Mid(z, 3, 1) * Mid(z, 7, 1)
sSum = sSum + Mid(z, 2, 1) * Mid(z, 8, 1)
sSum = sSum + Mid(z, 9, 1)
sSum = sSum / 11
    If sSum <> 0 Then
        calcNum = False
    Else
        calcNum = True
    End If

End Function

JR