Applying a find/replace function

[Arr]

Good day to all,
I import a monthly financial statement from txt into a table and designate fields based on a specification called in a form. One field is named "Amount".



The "Amount" field contains charges that are 8 characters long and have either a bracket or letter as the last character. An example is "0000376{" or "0000178K".

I have a conversion table to designate what the last character stands for and I would like to replace the amount character with the number assigned. An example is ("{" = 0) to "00003762" or ("K" = -2) to "00001782". Based upon the conversion table, the amount charges represent either debits (+) or credits (-). I also included a sample of the data in my field.

How can I write the replace function to run through the Amount field and replace the last character on each charge? And how can I call it on my form?

Thanks in advance

[dcrake]

Here is a function that will replace the character as described above

 
Public Function RepChar(AnyString As String) As String
'This function examines the last character in the string
'And by using the case statement it reeplaces that character
'with the appropriate value
Select Case Right(AnyString, 1)
    Case "{" Or "}"
        AnyString = Replace(AnyString, "{", 0)
        AnyString = Replace(AnyString, "}", 0)
    Case "A"
        AnyString = Replace(AnyString, "A", 1)
    Case "B"
        AnyString = Replace(AnyString, "B", 2)
    Case "C"
        AnyString = Replace(AnyString, "C", 3)
    Case "D"
        AnyString = Replace(AnyString, "D", 4)
    Case "E"
        AnyString = Replace(AnyString, "E", 5)
    Case "F"
        AnyString = Replace(AnyString, "F", 6)
    Case "G"
        AnyString = Replace(AnyString, "G", 7)
    Case "H"
        AnyString = Replace(AnyString, "H", 8)
    Case "I"
        AnyString = Replace(AnyString, "I", 9)
    Case "J"
        AnyString = Replace(AnyString, "A", -1)
    Case "K"
        AnyString = Replace(AnyString, "B", -2)
    Case "L"
        AnyString = Replace(AnyString, "C", -3)
    Case "M"
        AnyString = Replace(AnyString, "D", -4)
    Case "N"
        AnyString = Replace(AnyString, "E", -5)
    Case "O"
        AnyString = Replace(AnyString, "F", -6)
    Case "P"
        AnyString = Replace(AnyString, "G", -7)
    Case "Q"
        AnyString = Replace(AnyString, "H", -8)
    Case "R"
        AnyString = Replace(AnyString, "I", -9)
End Select
RepChar = AnyString
    
End Function

However, I don't understand the logic fully. To use in a form or in a query you would do the following

Form

Me.Amount = RepChar([Amount])

Query

Amt:RepChar([Amount])


Note: both { and } have the same replacement value.


David

[Arr]

Thanks for posting this reply. It was helpful.

As far as the logic, I wanted to make sure I identified it correctly on my form after the function / procedure was written. So they were really two separate questions.

I noticed the values of { and } are both zero but I am applying a multiplier to distinguish the debits from credits so that should eliminate any duplicates.

Thanks again.