Split 1 text field into multiple values

[salphonso]

Hello everyone,

I am having trouble trying to put in a VIN decoder for my company. Basically the VIN is entered into 1 column in our contract table. I need a way to split that vin into 17 different variables.

For example if the VIN is 123456789ABCDEFGH then p1 = 1, p2 = 2, p3 = 3 and so on.

How can I split this field into 17 different values? Is it even possible?

Thanks in advance for any help.

Scott

 

[Brianwarnock]

Yep
The simple approach is to use the mid function
p1=mid(vin,1,1)
p2=mid(vin,2,1)
etc
you could use Left and Right for the first and last but why bother.

Brian

 

[salphonso]

Thanks Brian. Now it gets a little more complex after this. I need to assign a value based on what character it is if it is not a number. If it is A then it is = to 1 or U = 4, etc. Do I have to write an If statement for Case statement for each p#?

 

[boblarson]

Don't write a case statement for each number. Write a function to return the correct value based on the input.

 

[salphonso]

I am more on the networking side of things and I am diving into a screwed up access database . I have fixed many things in it but trying to add this is proving to be complicated for me anyways.

If I write a function to Decode the VIN how would I attach another function to the p#'s to give each a value if a letter is in that position?

 

[boblarson]

You could use what Brian wrote but changed thusly:

Public Function DecodeVIN(strVINItem As String) As String
...write your function to decode the individual item here
End Function

p1=DecodeVIN(mid(vin,1,1))
p2=DecodeVIN(mid(vin,2,1))

 

[Brianwarnock]

Hmm,
I was thinking along the lines of a query with all the new values in individual fields, Bob is correct to suggest a single Case function to convert the alpha something like

Function fvin(pnum)
If IsNumeric(pnum) Then
fvin = pnum
Exit Function
End If

Select Case pnum
Case "a"
fvin = 1
Case "u"
fvin = 4
etc
End Select

End Function

Then in the query p1:fvin(mid,1,1) etc

Brian

 

[salphonso]

The whole reason for splitting the VIN up all comes down to one thing. I need to run this formula to make sure its valid.

((p1 * 8) + (p2 * 7) + (p3 * 6) + (p4 * 5) + (p5 * 4) + (p6 * 3) + (p7 * 2) + (p8 * 10) + (p10 * 9) + (p11 * 8) + (p12 * 7) + (p13 * 6) + (p14 * 5) + (p15 * 4) + (p16 * 3) + (p17 * 2)) Mod 11

this will give me the remainder and the remainder needs to be equal to p9. If it is equal then the vin is valid if it is not equal then it is invalid. At this point I need to create a report that will print out all the information of the contract with the invalid vins so we can get them corrected.

 

[Brianwarnock]

Just in case you haven't done it yet I tried a simple test, using 2 functions as i think this is the way to go after your last post.

Function decodevin(pnum)
If IsNumeric(pnum) Then
decodevin = pnum
Exit Function
End If

Select Case pnum
Case "a"
decodevin = 1
Case "b"
decodevin = 7
End Select

End Function
Function testvin(vin)
p1 = decodevin(Mid(vin, 1, 1))
p2 = decodevin(Mid(vin, 2, 1))
p3 = decodevin(Mid(vin, 3, 1))
p4 = decodevin(Mid(vin, 4, 1))

'Vintest
If ((p1 * 1) + (p2 * 1) + (p3 * 1) + (p4 * 1)) Mod 2 = p3 Then
testvin = "valid"
Else: testvin = "invalid"
End If

End Function

The query includes the field validvintest:testvin(vin)

If you are still wrestling with it I hope this simplified version helps.

Brian

 

[salphonso]

Thank a bunch you all for the help. I got it working perfectly now.

Function decodevin(pnum)
    If IsNumeric(pnum) Then
       decodevin = pnum
    Exit Function
    End If
  
    Select Case pnum
        Case "A"
            decodevin = 1
        Case "B"
            decodevin = 2
        Case "C"
            decodevin = 3
        Case "D"
            decodevin = 4
        Case "E"
            decodevin = 5
        Case "F"
            decodevin = 6
        Case "G"
            decodevin = 7
        Case "H"
            decodevin = 8
        Case "J"
            decodevin = 1
        Case "K"
            decodevin = 2
        Case "L"
            decodevin = 3
        Case "M"
            decodevin = 4
        Case "N"
            decodevin = 5
        Case "P"
            decodevin = 7
        Case "R"
            decodevin = 9
        Case "S"
            decodevin = 2
        Case "T"
            decodevin = 3
        Case "U"
            decodevin = 4
        Case "V"
            decodevin = 5
        Case "W"
            decodevin = 6
        Case "X"
            decodevin = 7
        Case "Y"
            decodevin = 8
        Case "Z"
            decodevin = 9
    End Select
End Function
   
Function testvin(vin) As String
Dim p1 As Long
Dim p2 As Long
Dim p3 As Long
Dim p4 As Long
Dim p5 As Long
Dim p6 As Long
Dim p7 As Long
Dim p8 As Long
Dim p9 As Long
Dim p10 As Long
Dim p11 As Long
Dim p12 As Long
Dim p13 As Long
Dim p14 As Long
Dim p15 As Long
Dim p16 As Long
Dim p17 As Long
Dim r1 As Long

    'calculate check digit
    If IsNumeric(Mid(vin, 9, 1)) Then
        p9 = (Mid(vin, 9, 1))
    ElseIf Mid(vin, 9, 1) = "X" Then
        p9 = 10
    Else
        testvin = "invalid"
     Exit Function
    End If

    p1 = decodevin(Mid(vin, 1, 1))
    p2 = decodevin(Mid(vin, 2, 1))
    p3 = decodevin(Mid(vin, 3, 1))
    p4 = decodevin(Mid(vin, 4, 1))
    p5 = decodevin(Mid(vin, 5, 1))
    p6 = decodevin(Mid(vin, 6, 1))
    p7 = decodevin(Mid(vin, 7, 1))
    p8 = decodevin(Mid(vin, 8, 1))
    p10 = decodevin(Mid(vin, 10, 1))
    p11 = decodevin(Mid(vin, 11, 1))
    p12 = decodevin(Mid(vin, 12, 1))
    p13 = decodevin(Mid(vin, 13, 1))
    p14 = decodevin(Mid(vin, 14, 1))
    p15 = decodevin(Mid(vin, 15, 1))
    p16 = decodevin(Mid(vin, 16, 1))
    p17 = decodevin(Mid(vin, 17, 1))
    
    r1 = ((p1 * 8) + (p2 * 7) + (p3 * 6) + (p4 * 5) + (p5 * 4) + (p6 * 3) + (p7 * 2) + (p8 * 10) + (p10 * 9) + (p11 * 8) + (p12 * 7) + (p13 * 6) + (p14 * 5) + (p15 * 4) + (p16 * 3) + (p17 * 2)) Mod 11
    
    If (r1 = p9) Then
        testvin = "Valid"
    Else
        testvin = vin
    End If
            
End Function

Basically If decodes the vin using all the rules needed and then if its invalid it will display the vin if its valid it will say so. This will basically increase our productivity by a significant margin. Thanks again to those of you that helped me on this little project.

 

[Brianwarnock]

Basically If decodes the vin using all the rules needed and then if its invalid it will display the vin if its valid it will say so. This will basically increase our productivity by a significant margin. Thanks again to those of you that helped me on this little project.

You also display "invalid" if the checkdigit is invalid,this is one of extra interesting twists in the final solution. It is nice when a poster takes the help offered and uses it to solve the problem without asking us to dot all the i's and cross all the t's, it means they have understood and learnt.

Happy to have helped.

Brian