Attribute VB_Name = "ProcFuncs"
Option Compare Database


Public Function State_Abbrv(strItem As String) As String

' *****************************************
' * Takes a string containing either a U.S.
' * state name, a Canadian province name,
' * or a two-digit U.S. or Canadian Postal
' * Service abbreviation and returns the
' * other.  Enter TX and it returns Texas,
' * or enter Texas and it returns TX.
' *
' * Returns an error string for invalid
' * text. Returns the error message for
' * valid Canadian codes, e.g., BC.
' *
' * Displays an error dialog box for invalid
' * input.
' *
' * Author: Joseph "Rick" Reinckens II
' * www.DallasProducts.com
' * (c) 2008
' *
' * May be distributed without permission
' * as long as this copyright notice is
' * included, including the URL
' *****************************************
    
    
    Dim strOut As String
    strOut = "*** INVALID STRING ***"
    strItem = UCase(Trim(strItem))
    If Len(Trim(strItem)) = 2 Then

' *************************************
' * Convert abbreviation to full name *
' *************************************
       
        Select Case strItem
' ********
' * U.S. *
' ********
            Case "AL"
                strOut = "Alabama"
            Case "AK"
                strOut = "Alaska"
            Case "AZ"
                strOut = "Arizona"
            Case "AR"
                strOut = "Arkansas"
            Case "CA"
                strOut = "California"
            Case "CO"
                strOut = "Colorado"
            Case "CT"
                strOut = "Connecticut"
            Case "DE"
                strOut = "Delaware"
            Case "DC"
                strOut = "District of Columbia"
            Case "FL"
                strOut = "Florida"
            Case "GA"
                strOut = "Georgia"
            Case "HI"
                strOut = "Hawaii"
            Case "ID"
                strOut = "Idaho"
            Case "IL"
                strOut = "Illinois"
            Case "IN"
                strOut = "Indiana"
            Case "IA"
                strOut = "Iowa"
            Case "KS"
                strOut = "Kansas"
            Case "KY"
                strOut = "Kentucky"
            Case "LA"
                strOut = "Louisiana"
            Case "ME"
                strOut = "Maine"
            Case "MD"
                strOut = "Maryland"
            Case "MS"
                strOut = "Massachusetts"
            Case "MI"
                strOut = "Michigan"
            Case "MN"
                strOut = "Minnesota"
            Case "MS"
                strOut = "Mississippi"
            Case "MO"
                strOut = "Missouri"
            Case "MT"
                strOut = "Montana"
            Case "NE"
                strOut = "Nebraska"
            Case "NV"
                strOut = "Nevada"
            Case "NH"
                strOut = "New Hampshire"
            Case "NJ"
                strOut = "New Jersey"
            Case "NM"
                strOut = "New Mexico"
            Case "NY"
                strOut = "New York"
            Case "NC"
                strOut = "North Carolina"
            Case "ND"
                strOut = "North Dakota"
            Case "OH"
                strOut = "Ohio"
            Case "OK"
                strOut = "Oklahoma"
            Case "OR"
                strOut = "Oregon"
            Case "PA"
                strOut = "Pennsylvania"
            Case "RI"
                strOut = "Rhode Island"
            Case "SC"
                strOut = "South Carolina"
            Case "SD"
                strOut = "South Dakota"
            Case "TN"
                strOut = "Tennessee"
            Case "TX"
                strOut = "Texas"
            Case "UT"
                strOut = "Utah"
            Case "VT"
                strOut = "Vermont"
            Case "VA"
                strOut = "Virginia"
            Case "WA"
                strOut = "Washington"
            Case "WV"
                strOut = "West Virginia"
            Case "WI"
                strOut = "Wisconsin"
            Case "WY"
                strOut = "Wyoming"
' **********
' * Canada *
' **********
            Case "AB"
                strOut = "Alberta"
            Case "BC"
                strOut = "British Columbia"
            Case "MB"
                strOut = "Manitoba"
            Case "NB"
                strOut = "New Brunswick"
            Case "NL"
                strOut = "Newfoundland and Labrador"
            Case "NT"
                strOut = "Northwest Territories"
            Case "NS"
                strOut = "Nova Scotia"
            Case "NU"
                strOut = "Nunavut"
            Case "ON"
                strOut = "Ontario"
            Case "PE"
                strOut = "Prince Edward Island"
            Case "QC"
                strOut = "Quebec"
            Case "SK"
                strOut = "Saskatchewan"
            Case "YT"
                strOut = "Yukon"
        End Select
    Else
' *************************************
' * Convert full name to abbreviation *
' *************************************

        Select Case strItem
' ********
' * U.S. *
' ********
            Case "ALABAMA"
                strOut = "AL"
            Case "ALASKA"
                strOut = "AK"
            Case "ARIZONA"
                strOut = "AZ"
            Case "ARKANSAS"
                strOut = "AR"
            Case "CALIFORNIA"
                strOut = "CA"
            Case "COLORADO"
                strOut = "CO"
            Case "CONNECTICUT"
                strOut = "CT"
            Case "DELAWARE"
                strOut = "DE"
            Case "DISTRICT OF COLUMBIA"
                strOut = "DC"
            Case "FLORIDA"
                strOut = "FL"
            Case "GEORGIA"
                strOut = "GA"
            Case "HAWAII"
                strOut = "HI"
            Case "IDAHO"
                strOut = "ID"
            Case "ILLINOIS"
                strOut = "IL"
            Case "INDIANA"
                strOut = "IN"
            Case "IOWA"
                strOut = "IA"
            Case "KANSAS"
                strOut = "KS"
            Case "KENTUCKY"
                strOut = "KY"
            Case "LOUISIANA"
                strOut = "LA"
            Case "MAINE"
                strOut = "ME"
            Case "MARYLAND"
                strOut = "MD"
            Case "MASSACHUSETTS"
                strOut = "MS"
            Case "MICHIGAN"
                strOut = "MI"
            Case "MINNESOTA"
                strOut = "MN"
            Case "MISSISSIPPI"
                strOut = "MS"
            Case "MISSOURI"
                strOut = "MO"
            Case "MONTANA"
                strOut = "MT"
            Case "NEBRASKA"
                strOut = "NE"
            Case "NEVADA"
                strOut = "NV"
            Case "NEW HAMPSHIRE"
                strOut = "NH"
            Case "NEW JERSEY"
                strOut = "NJ"
            Case "NEW MEXICO"
                strOut = "NM"
            Case "NEW YORK"
                strOut = "NY"
            Case "NORTH CAROLINA"
                strOut = "NC"
            Case "NORTH DAKOTA"
                strOut = "ND"
            Case "OHIO"
                strOut = "OH"
            Case "OKLAHOMA"
                strOut = "OK"
            Case "OREGON"
                strOut = "OR"
            Case "PENNSYLVANIA"
                strOut = "PA"
            Case "RHODE ISLAND"
                strOut = "RI"
            Case "SOUTH CAROLINA"
                strOut = "SC"
            Case "SOUTH DAKOTA"
                strOut = "SD"
            Case "TENNESSEE"
                strOut = "TN"
            Case "TEXAS"
                strOut = "TX"
            Case "UTAH"
                strOut = "UT"
            Case "VERMONT"
                strOut = "VT"
            Case "VIRGINIA"
                strOut = "VA"
            Case "WASHINGTON"
                strOut = "WA"
            Case "WEST VIRGINIA"
                strOut = "WV"
            Case "WISCONSIN"
                strOut = "WI"
            Case "WYOMING"
                strOut = "WY"
' **********
' * Canada *
' **********
            Case "ALBERTA"
                strOut = "AB"
            Case "BRITISH COLUMBIA"
                strOut = "BC"
            Case "MANITOBA"
                strOut = "MB"
            Case "NEW BRUNSWICK"
                strOut = "NB"
            Case "NEWFOUNDLAND AND LABRADOR"
                strOut = "NL"
            Case "NORTHWEST TERRITORIES"
                strOut = "NT"
            Case "NOVA SCOTIA"
                strOut = "NS"
            Case "NUNAVUT"
                strOut = "NU"
            Case "ONTARIO"
                strOut = "ON"
            Case "PRINCE EDWARD ISLAND"
                strOut = "PE"
            Case "QUEBEC"
                strOut = "QC"
            Case "SASKATCHEWAN"
                strOut = "SK"
            Case "YUKON"
                strOut = "YT"
        End Select
    End If
    
    If strOut = "*** INVALID STRING ***" Then
        MsgBox ("Invalid State String!")
    End If
    
    State_Abbrv = strOut
End Function 'State_Abbrv(strItem As String)