two if statements, Not sure how to write

[hartmut77]

Hello

Thanks to Lee Smith I have some headway in my project. I am stumped because in the VIN number there are some inconsistencies. namely a Dodge and a BMW. So I have to make a calculation that names the car a "BMW" only if the first digit of the VIN number is a "W" and in the Second number of the VIN number is a "B"-- But if the first number is 1 or a 4 and the second number is a "B" it is a Dodge. Not sure the best way to write this. Help please. and of course thank you.

Dave

[Henry]

You can try to use case statement, it will be easy for next time you add in other car to the script. From my understanding, if the VIN number start with "WB" then is "BMW", if the VIN number start with "1B" or "4B", then it will become "Dodge". Is that right? If right then the script below you can try it.

case( left(VIN , 2) = "WB", "BMW",

left(VIN, 2) = "1B" or left(VIN, 2) = "4B", "Dodge")

Regards,

Henry

[hartmut77]

Thanks Henry

That works fine but I would like to put the results in another field called "second"

Is it possible to write a bigger statement like you did only with the following data:

Audi (A), BMW (:, Buick (4), Cadillac (6), Chevrolet (1), Chrysler ©, Dodge (:, Ford (F)

The BMW and The Dogde are because the 2nd digit in the VIN is a "B" but the country or origin (the first digit are different)

Just asking because your way seems faster.

Thanks very much

Dave

[Henry]

Do you mean the first digit is "A" for audi or the "A" is the 2nd digit? If "A" Is the 2nd digit, then just expand the script i write just now.

case( left(VIN , 2) = "WB", "BMW",

left(VIN, 2) = "1B" or left(VIN, 2) = "4B", "Dodge",

middle(VIN, 2, 1)= "A", "Audi",

middle(VIN, 2, 1)= "4", "Buick",

middle(VIN, 2, 1)= "6", "Cadillac",

middle(VIN, 2, 1)= "1", "Chevrolet",

middle(VIN, 2, 1)= "C", "Chrysler",

middle(VIN, 2, 1)= "F", "Ford")

If it is 1st digit, then just change all the middle() become left(VIN, 1).

Just ask if have any question, hope can help you

Regards,

Henry

[LaRetta]

Hi Dave,

Instead of hard-coding your vehicle makes in a calculation (which would need to be modified each time you added a Make and might become a bit resource-hungry), you might wish to consider attaching a related database of Makes.

This would make it much easier to adjust as needed and the unique aspects of the VIN could be more easily tracked. Just a thought...

LaRetta

[Anatoli]

LaRetta is basically saying, that variable value should be entry in database and not part of any calculation formula.

[DonH]

Putting all the data into a db is the best choice, even if it does complicate your "if" statement.

You will then have to loop over the list of beginning numbers/letters to get the proper one for the entered VIN. However, if you have some that need the 1st 2 characters and others that just need the 2nd character you will have to have a way to distinguish them.

Maybe another field that says if you need the 1st 2 or just the 2nd character. Then seperate IF statements for them.

[BobWeaver]

A loop isn't necessary. Just create a calculated field that extracts the first two characters of the VIN and use that as the primary key to the related file. Then, you can access the vehicle make directly from the related record.

[LaRetta]

Anatoli said... that variable value should be entry in database and not part of any calculation formula. Bob said... A loop isn't necessary. Just create a calculated field that extracts the first two characters of the VIN and use that as the primary key to the related file.

I was in a bit of hurry, but thought a relationship would be clear without a lot of explanation. Guess I was wrong ... sorry about that. Thanks for clarifying it for me guys!

My point was that, if the first two characters designate the Make, your related db could contain the Make and 'VinID' (which would only contain these two 'unique' characters). If more than one 2-character applies to a vehicle (such as Dodge 1B and 4B), simply enter 1B in the VinID, hit enter to insert a carriage return and type your 4B. Once your Makes db in complete, create a calculation (text) in your Main db with something like: Left( CompleteVinNumber, 2). Create a relationship from this calculation to your VinID::Makes.

When a User types a Vin# in CompleteVinNumber, the related Make will appear (relate). No scripts needed. I hope that cleared up my comments.

LaRetta

[hartmut77]

Thank you all, This has been very helpful. I am struggling away at this - I am quite amazed that this VIN thing is not more straitforward. I am also amazed that there is precious little out there on the subject. I will be asking more questions here . This is hard for me but you have made it so much better and a great learning experience. If anyone has VIN experience please By all means pipe in.

Thanks

Dave

[DonH]

Maybe I'm misunderstanding the premise. It sounded like there were 2 conditions. 1) The VIN needs first 2 characters to determine make. 2) VIN needs only 2nd character.

[Henry]

If use the calculation field to link within the db, like LaRetta said. It can check not only first 2 characters, but more. Just like the Type A Head technique.

Henry

[LaRetta]

My apologies. I provided a solution based upon a match on the first two characters. Thanks Henry for pointing that out.

If some Vehicles match on the first character and some match on the second (or the first two characters), you could enter either one or two chacacters to distinguish them (in your VinID in your related db) as:

A---Audi

WB---BMW

4---Buick

6---Cadillac

1---Chevrolet

C---Crysler

1B