none
How do I replace the last character in a text field that have various lenghts with another character RRS feed

  • Question

  • I have a table that has a AMT field that contains various numeric characters and sixes ending  in a range of numbers from 0 to 9.  I need to replace those last characters with their letter equivalent for example 0=!, 1=J, 2=K, 3=L, 4=M, 5=N, 6=O, 7=P, 8=Q, AND 9=R.  I need to replace these numbers with the letter characters, can anybody help using either VBA or an Update Query?
    Wednesday, October 11, 2017 6:37 PM

All replies

  • You can create an Update query based on the table.

    Add the AMT field to the query grid.

    In the Update to: line, enter

    Left([AMT],Len([AMT])-1) & Chr(Right([AMT],1)+73)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, October 11, 2017 8:18 PM
  • Hello,

    I think it is more flexible if you create a UDF.

    Function demo(field As String) As String
    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add "0", "!"
    dict.Add "1", "J"
    dict.Add "2", "K"
    dict.Add "3", "L"
    dict.Add "4", "M"
    dict.Add "5", "N"
    dict.Add "6", "O"
    dict.Add "7", "P"
    dict.Add "8", "Q"
    dict.Add "9", "R"
    demo = Left(field, Len(field) - 1) & dict(Right(field, 1))
    End Function

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 12, 2017 2:46 AM
    Moderator