AnsweredAssumed Answered

Get a State Abbreviation from the State Name

Question asked by SteveMartino on Apr 18, 2016
Latest reply on Apr 18, 2016 by SteveMartino

Hello Forum.

As a mental exercise and to prevent errors, I was wondering if there was an 'easier' way to get a state abbreviation from a state name (American database only).

I have a text field State, with all 50 states, and a text field State_abr.  When a user is creating a new record, they can only select the state from the drop down list.  However, I would then like to auto enter the abbreviation for the state.  I obviously can't do this by parsing as the abbreviation naming convention isn't the same based on the state name, i.e. first 2 letters, or first and last letter.

The only way I can think of is to hard code a calculation using Substitute (which of course I could've already accomplished in the time it takes me to type out this question and wait for responses).

I was thinking if it were Excel, I would have a list of States with the abbreviations in the next row and use the lookup function (but that is also hard coded).

I also thought, but did not try, a separate table of abbreviations, or a separate table occurrence, with a conditional value list.  I think this would get me the proper abbreviation, then have it auto entered.

I'm more concerned with best method because I feel it could be used in a different, but similar type of situation, should a need ever arise.

Any thoughts/help or guidance is always appreciated.