AnsweredAssumed Answered

Help with substitute function

Question asked by PROFT on Oct 1, 2009
Latest reply on Oct 1, 2009 by PROFT


Help with substitute function


I'm working on a database with addresses.  The source data has the states all spelled out.  I need to convert them to abbreviations.  I went File>Manage>Database and I changed the field to a calculation field and input the following substitute formula.  The data has not changed.  I cannot figure out what I've missed.  Any help would be greatly appreciated!


I have the calculation result as text, number of repetitions as 1, the do not evaluate if all referenced fields are empty is checked.  Here is the function data that's in the box:


Substitute(State; ["ALABAMA"; "AL"]; ["ALASKA"; "AK"]; ["ARIZONA"; "AZ"]; ["ARKANSAS"; "AR"]; ["CALIFORNIA"; "CA"]; ["COLORADO"; "CO"]; ["CONNECTICUT"; "CT"]; ["DELAWARE"; "DE"]; ["DISTRICT OF COLUMBIA"; "DC"]; ["FLORIDA"; "FL"]; ["GEORGIA"; "GA"]; ["HAWAII"; "HI"]; ["IDAHO"; "ID"]; ["ILLINOIS"; "IL"]; ["IOWA"; "IA"]; ["KANSAS"; "KS"]; ["KENTUCKY"; "KY"]; ["LOUISIANA"; "LA"]; ["MAINE"; "ME"]; ["MARYLAND"; "MD"]; ["MASSACHUSETTS"; "MA"]; ["MICHIGAN"; "MI"]; ["MINNESOTA"; "MN"]; ["MISSISSIPPI"; "MS"]; ["MISSOURI"; "MO"]; ["MONTANA"; "MT"]; ["NEBRASKA"; "NE"]; ["NEVADA"; "NV"]; ["NEW HAMPSHIRE"; "NH"]; ["NEW JERSEY"; "NJ"]; ["NEW MEXICO"; "NM"]; ["NEW YORK"; "NY"]; ["NORTH CAROLINA"; "NC"]; ["NORTH DAKOTA"; "ND"]; ["OHIO"; "OH"]; ["OKLAHOMA"; "OK"]; ["OREGON"; "OR"]; ["PENNSYLVANIA"; "PA"]; ["PUERTO RICO"; "PR"]; ["RHODE ISLAND"; "RI"]; ["SOUTH CAROLINA"; "SC"]; ["SOUTH DAKOTA"; "SD"]; ["TENNESSEE"; "TN"]; ["TEXAS"; "TX"]; ["UTAH"; "UT"]; ["VERMONT"; "VT"]; ["VIRGINIA"; "VA"]; ["WASHINGTON"; "WA"]; ["WEST VIRGINIA"; "WV"]; ["WISCONSIN"; "WI"]; ["WYOMING"; "WY"])


I don't receive any kind of error message, but the text in the field is not being substituted.