Validate field in VBA

Navigation
0

In Mozambique there is a unique tax identification number (nuit), and this number takes 9 digits. How would an application validate nuit under the following conditions?

  • If there are more (+) or less (-) than nine (9) digits, say "nuit invalid."

  • Should only accept numbers, if they are not numbers say "please enter only numbers"

I have the algorithm here: 

Private Sub CommandButton1_Click()

v = validarnuit(TextBox1.Text)
MsgBox v

End Sub
Function validarnuit(nuit)
Dim cd As Double
Dim valido As String
valido = "Nuit Invalido"
If Len(nuit) = 9 Then
If IsNumeric(nuit) Then
If nuit <> 0 Then

cd = Mid(nuit, 8, 1) * 2
cd = cd + Mid(nuit, 7, 1) * 3 + _
cd = cd + Mid(nuit, 6, 1) * 4 + _
cd = cd + Mid(nuit, 5, 1) * 5 + _
cd = cd + Mid(nuit, 4, 1) * 6 + _
cd = cd + Mid(nuit, 3, 1) * 7 + _
cd = cd + Mid(nuit, 2, 1) * 2 + _
cd = cd + Mid(nuit, 1, 1) * 3 + _
cd = 11 - (cd Mod 11)
If cd = 9 Then
valido = "good"
End If
End If
End If
End If
validarnuit = valido
End Function

If the cd fore equals 11, it is false and not valid; if it is equal to 10 it also does not validate; only valid if cd equals 9. Image if the division equals 2, this would be 11-2 = cd 9 and valid. I tried to use this code on top but it does not give 9. Does anyone have an effective idea of how to do this?

    
asked by anonymous 10.06.2014 / 11:16

1 answer

2

(The following answer is relative to this editing )

Assuming you are receiving data in a TextBox you can use the Len to determine the size of a string : 

tamanhoEsperado = 9 ' Tamanho do número esperado 
tamanho = Len(TextBox.Value)
If (tamanho > tamanhoEsperado) Then
    ' Invalido
ElseIf (tamanho < tamanhoEsperado) Then
    ' Invalido
Else
    ' Válido
End If

To be able to determine if they are just digits, VBA has the IsNumeric that you can use as follows: 

If(IsNumeric(TextBox.Value)) Then
     ' São apenas números, grave na base de dados
EndIf

Edit:

To insert the field into the database: 

' Abra a ligação
Set conn = New ADODB.Connection
conn.ConnectionString = "" 'Coloque aqui a sua connection string
conn.Open

' Crie a instrução INSERT INTO.
statement = "INSERT INTO nuit " & _
    "(nuit) " & _
    " VALUES (" & _
    "'" & txtnuit.Value & "', " & _
    ")"

' Execute a instrução.
conn.Execute statement, , adCmdText

' Feche a ligação.
conn.Close
    
10.06.2014 / 11:34
How do I insert anchors to my page? Loading with Ajax