Looking for Code to Check the Vin of A Vehicle

[EileenL]

I have a table that I would like the system to run the 9th digit check vin on after the user enters the vin. I not that versed in the coding so I was wondering if there is code already out there that i can add to Access Module and call it from the vehicle information screen.

thanks
Eileen

 

[WayneRyan]

Eileen,

You can use the AfterUpdate event of the VIN field to:

If Not IsNull(DLookUp("[VIN]", "tblVINs", "[VIN] = '" & Me.VIN & "'") Then
   MsgBox("Hey, that VIN exists!")
End If

Wayne

 

[EileenL]

9th digit check vin

there is a specific calculation that you use to check to see if a vin is valid To each letter there is an assigned number, then there is a weight for each position of a digit in a vin which is then multiplied by the actual number all of which are summed and then divided/11 the remainder is your check digit in the 9th position. If the remainder is 0-9 then the check digit is a numeric value and if it is 10 then it is X.

I think I have to setup an array for the letters and one for each position but I am not sure how to go about it.

Thanks
Eileen

 

[Mile-O]

VIN? What's that?

 

[NJudson]

VIN? What's that?

Vehicle Identification Number

 

[Treason]

I didnt think arrays were possible in access

 

[WayneRyan]

Arrays are possible!

But what's this formula business?

Wayne

 

[EileenL]

The formula is this

Assign to each number in the vehicle identification number its actual mathmatical value and assign to each letter the value specified in the following;

A=1 D=4 G=7 K=2 N=5 S=2 V=5 Y=8
B=2 E=5 H=8 L=3 P=7 T=3 W=6 Z=9
C=3 F=6 J=1 M=4 R=9 U=4 X=7

Multiply the assigned value for each character in the vehicle identification number by the weight factor as follows

1st=8 6th=3 11th=8 16th=3
2nd=7 7th=2 12th=7 17th=2
3rd=6 8th=10 13th=6
4th=5 9th=0 14th=5
5th=4 10th=9 15th=4

Add the products and divide by 11. The remainder should be the check-digit in the ninth position of the vehicle identification number. If the remainder is 0-9, the check digit is the numeric value; if the remainder is 10, the check-digit is X

I know I have to split the vin number but there is not delimeter in the field, I think I have to set up an array for the position value and then change any letter within the vin to the numerical value and the divide by 11 using the mod() to return a remainder value which is compared to the value of the 9th position.

I want to be able to call this on a afterupdate property for the field if the user enters a wrong vin no then a msgbox will appear asking them to correct it if it is entered correctly then they move to the next field.

I know I have to either split the value in the vin no field, use an array to assign the proper numerical value to each digit and then proceed. I tried the split() function but it does require a delimeter to segregate each value with in the vin I think I can use the left, right and mid functions to accomplish the same. But not sure how to use the array()

Thanks for the help Everyone
Eileen

 

[WayneRyan]

Eileen,

Didn't do any error checking (I never do ...),
Didn't document it (I never do ...),
Didn't try it (I never do ...),

but this should be close.

Use the AfterUpdate of the VIN field:

If Not CheckVIN(Me.VIN) Then
MsgBox("That VIN is wrong.")
End If

Public CheckVIN (VIN As String) As Boolean
Dim i as Long
Dim lngSum As Long
Dim lngWeight As String
Dim lngMultiplier As String
Dim Temp As Long

lngWeight = "A1B2C3D4E5F6G7H8J1K2L3M4N5P7R9S2T3U4V5W6X7Y8Z9"
lngMultiplier = "87654321098765432"

lngSum = 0
For i = 1 To Len(VIN)
   Temp = CLng(Mid(lngWeight, InStr(1, lngWeight, Mid(VIN, i, 1)) + 1, 1))
   If i <> 10 Then
      lngSum = lngSum + Temp * CStr(Mid(lngMultiplier, i, 1))
   Else
      lngSum = lngSum + Temp * 10
   End If
   Next i
Temp = lngSum Mod 11
If (Temp = 10 And Mid(VIN, 9, 1) = "X") Or (Mid(VIN, 9, 1) = CStr(Temp) Then
   CheckVIN = True
Else
   CheckVIN = False
End If
End Function

Wayne

 

[KevinPutman]

Eileen,

Didn't do any error checking (I never do ...),
Didn't document it (I never do ...),
Didn't try it (I never do ...),

but this should be close.

Use the AfterUpdate of the VIN field:

If Not CheckVIN(Me.VIN) Then
MsgBox("That VIN is wrong.")
End If

Public CheckVIN (VIN As String) As Boolean
Dim i as Long
Dim lngSum As Long
Dim lngWeight As String
Dim lngMultiplier As String
Dim Temp As Long

lngWeight = "A1B2C3D4E5F6G7H8J1K2L3M4N5P7R9S2T3U4V5W6X7Y8Z9"
lngMultiplier = "87654321098765432"

lngSum = 0
For i = 1 To Len(VIN)
   Temp = CLng(Mid(lngWeight, InStr(1, lngWeight, Mid(VIN, i, 1)) + 1, 1))
   If i <> 10 Then
      lngSum = lngSum + Temp * CStr(Mid(lngMultiplier, i, 1))
   Else
      lngSum = lngSum + Temp * 10
   End If
   Next i
Temp = lngSum Mod 11
If (Temp = 10 And Mid(VIN, 9, 1) = "X") Or (Mid(VIN, 9, 1) = CStr(Temp) Then
   CheckVIN = True
Else
   CheckVIN = False
End If
End Function

Wayne

The above code is close, but has a few problems:

1) The 2nd line should be:
Public Function CheckVIN (VIN As String) As Boolean

2) The 1st If statement should be:
If i <> 8 Then

3) The 2nd If statement has parenthesis that it should not have:
If Temp = 10 And Mid(VIN, 9, 1) = "X" Or Mid(VIN, 9, 1) = CStr(Temp) Then

4) The code did not take into account if the VIN character was already numeric and did not need to be converted and if treated as a string, did not include the equivalent weight. I added an extra if statement to account for this.

5) Lastly, I added an if statement to exit the function resulting in a return of false if the VIN is not 17 characters. If the VIN was missing a zero at the end, it would still return as valid as it was. I am not sure what would have happened if the VIN was too long.

Here is the corrected code:

Option Explicit

Const MODULE_NAME As String = "modVIN"

Public Function CheckVIN(VIN As String) As Boolean
Dim i As Long
Dim lngSum As Long
Dim lngWeight As String
Dim lngMultiplier As String
Dim Temp As Long

lngWeight = "A1B2C3D4E5F6G7H8J1K2L3M4N5P7R9S2T3U4V5W6X7Y8Z9"
lngMultiplier = "87654321098765432"

If Len(VIN) <> 17 Then
    Exit Function
End If

lngSum = 0
For i = 1 To Len(VIN)
   If IsNumeric(Mid(VIN, i, 1)) Then
      Temp = Mid(VIN, i, 1)
   Else
      Temp = CLng(Mid(lngWeight, InStr(1, lngWeight, Mid(VIN, i, 1)) + 1, 1))
   End If
   If i <> 8 Then
      lngSum = lngSum + Temp * CStr(Mid(lngMultiplier, i, 1))
   Else
      lngSum = lngSum + Temp * 10
   End If
   Next i
Temp = lngSum Mod 11
If Temp = 10 And Mid(VIN, 9, 1) = "X" Or Mid(VIN, 9, 1) = CStr(Temp) Then
   CheckVIN = True
Else
   CheckVIN = False
End If
End Function