Jump to content

two if statements, Not sure how to write


This topic is 7582 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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... smile.gif

LaRetta

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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. crazy.gif Guess I was wrong ... sorry about that. Thanks for clarifying it for me guys! wink.gif

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. smile.gif

LaRetta

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

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

Link to comment
Share on other sites

This topic is 7582 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.