Text function

[Robbydobbs]

I know there is a way to do this but I'm not sure which text functions does it. I have a VIN (vehicle Identification Number) field which is 17 characters long. The 10th Digit is the year of the vehicle.

VIN: 1FAFP71X4YBN23456 (Y)Year is 2000

Codes:

A=1980

B=1981

C=1982

and so on.

How do I setup this field (VIN) to find the year by the tenth digit using a text function?

[BobWeaver]

Try this:

Position("ABCDEFGHIJKLMNOPQRSTUVWXYZ",Middle(VIN,10,1),1,1)+1979

[Robbydobbs]

That works great. Thank you so much.

[Lee Smith]

Hi Robbydobbs,

  Quote

That works great. Thank you so much

Does it?

Y = 2004 by this calculation which is correct if A = 1980

Which is the correct answer?

Lee

[Robbydobbs]

Actually, 2004 would be represently by a number - (4), anything after "Y" is represented by a number. That works too, because when I put 2 into the equation as the 10th digit, it came back with 2002.

[Lee Smith]

Please explain to me how you can change a alpha character for a numberic and get the right answer when you or using alpha character as the key to the whole thing.

In other words, using your vin number and the calculation supplied by Bob Weaver, I get the answer 2004. If I Substitute the [color:"red"] 2 for the [color:"red"] Y the calculation returns [color:"red"] 1979

To me this would be the correct answer, as there wasn't anything to add to the 1979.

Did you make this an If statement or Case Statement to test for this condition?

Lee

[Robbydobbs]

The DMV (Department of Motor Vehicles) doesn't use every character in the alphabet to decipher the year, the only characters they use are:

A,B,C,D,E,F,G,H,J,K,L,M,N,P,R,S,T,V,W,X,and Y.

So if the 10th digit is a L, then the year is 1990 because A = 1980 + 10 more characters puts L at 1990. Then anything after Y, would be a number, "1".

Sorry if this wasn't exactly clear, I guess I should of spelled it out clearer for those of you who are trying to figure this out.

[Lee Smith]

Now it makes sense

Thanks

Lee