How do I replace one occurrence of a character in a text filed with another character

[Clint!]

How do I replace one occurrence of a character in a text field with another character using a update query in Access 2010. I am trying to change the last number in a text filed to a letter that represents that number. For instance the number in a text field is "2345". I want to only replace the last character "5" with a letter equivalent of "N". the new field contents will be "234N". Can anybody please rescue me from this deadlock?

[orange]

Do you
-always have a text field with 4 digits
-always want to replace the character in rightmost position with "N"?

YourField = Left(Yourfield,3) + "N"

Perhaps you could tell us all the conditions involved.

[Clint!]

Thank you, but the text field is not static in length and will have vaious amounts. Any help would be appreciated

[orange]

In order to get specific help, you'll have to provide some examples showing your data requirement.

[Clint!]

In order to get specific help, you'll have to provide some examples showing your data requirement.

Sure, in the below listed table I need to replace the last character in the AMT field to its letter equivalent where 0=!, 4=P, and R=9. But, first I will remove the negative "-" sign. The last character can ranged from 0 to 9 and when replaced with the letter equivalent they will signify an negative number.

Table1

name	sales	AMT
jDoe1	IN	-169
Jdoe2	DC	-74584
JDoe3	VA	-125800

[orange]

Sounds like you are moving data from PC to some computer using EBCDIC coding.

I recall an older post with

'To convert the zoned ASCII field which results from an EBCDIC to ASCII character
'translation to a leading sign numeric field, inspect the last digit in the field.
'If it's a "{" replace the last digit with a 0 and make the number positive.
'If it's an "A" replace the last digit with a 1 and make the number positive,
'if it's a "B" replace the last digit with a 2 and make the number positive, etc., etc.
'If the last digit is a "}" replace the last digit with a 0 and make the number negative.
'If it's a "J" replace the last digit with a 1 and make the number negative,
'if it's a "K" replace the last digit with a 2 and make the number negative, etc., etc.
'Follow these rules for all possible values.  You could do this with a look-up table
'or with IF or CASE statements.  Use whatever method suits you best for the language
'you are using.  In most cases you should put the sign immediately before the
'first digit in the field.  This is called a floating sign, and is what most
'PC programs expect.  For example, if your field is 6 bytes, the value -123
'should read "  -123" not "-  123".

Sounds like you're going in the opposite direction, or something similar.

You mention The last character can ranged from 0 to 9 and when replaced with the letter equivalent they will signify an negative number. but only show 0, 4 and 9 ---what about 3, 5, or 6???

[Clint!]

That is correct i am going in the other direction. The example contains only 0,4,9 but hopefully you could see the concept.

[orange]

I have a function(deals with positive and negative numbers), but I'm not sure of your conversion equivalents. Do you have a complete list?

The following shows the "last digit" transformation.
?asc_ebcd("-74589")
-7458R

?asc_ebcd("54668")
5466H

?asc_ebcd("-6554")
-655M

?asc_ebcd("-300")
-30}

I am using the following conversion:

40  arrPos = "{ABCDEFGHI"    ' equates to +0123456789
50  arrNeg = "}JKLMNOPQR"  ' equates to -0123456789

[Micron]

This is interesting. I've never heard of EBCDIC and will have to check it out. PMFJI, but I might handle negatives using Sgn, i.e. multiply by -1
IF Sng([AMT]) = -1 Then [AMT] = [AMT] * -1
before using the Replace function to replace the last character with the DLookup value from a table of replacement values. Then again, no doubt Orange is thinking "Been there, done that"

[orange]

Micron,

These are strings in this set up. So multiplication isn't an option.
I'm using the representation as per the graphic I attached in last post.
But it seems the OP has a different translate table??

Here's my function .

'---------------------------------------------------------------------------------------
' Procedure : ASC_EBCD
' Author    : mellon
' Date      : 11-Oct-2017
' Purpose   : To convert a zoned ascii value to its ebcdic equivalent
'
'Parm:  strIn  --  a string of digits with optional leading sign
'
' If strIn has a leftmost "-", it is negative, so use the arrNeg array to resolve the Ebcdic
' If strIn has a leftmost " "  or "+", it is positive, so use the arrPos array to resolve the Ebcdic
'---------------------------------------------------------------------------------------
'
Function ASC_EBCD(strIN As String) As String

    Dim arrPos As String
    Dim arrNeg As String
    Dim ChrToChange As String

10  On Error GoTo ASC_EBCD_Error

20  strIN = Trim(strIN)      'remove any leading/trailing spaces
30  ChrToChange = Right(strIN, 1) + 1
40  arrPos = "{ABCDEFGHI"    ' equates to +0123456789
50  arrNeg = "}JKLMNOPQR"    ' equates to -0123456789

60  If Left(strIN, 1) = "-" Then     'we're dealing with negative numbers
70      ASC_EBCD = Left(strIN, Len(strIN) - 1) & Mid(arrNeg, ChrToChange, 1)
80  ElseIf Left(strIN, 1) = "+" Then  'there is a plus sign, remove it
90      strIN = Mid(strIN, 2)
100     ASC_EBCD = Left(strIN, Len(strIN) - 1) & Mid(arrPos, ChrToChange, 1)
110 Else                                 'no sign involved
120     ASC_EBCD = Left(strIN, Len(strIN) - 1) & Mid(arrPos, ChrToChange, 1)
130 End If
    'Debug.Print ASC_EBCD            'for debugging
ASC_EBCD_Exit:
140 Exit Function

ASC_EBCD_Error:
150 If err.number = 424 Then
160     MsgBox "Invalid or No parameter was input" & " in line " & Erl & " (" & err.Description & ") in procedure ASC_EBCD of Module AWF_Related"
170 Else
180     MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure ASC_EBCD of Module AWF_Related"
190  End If   
200 Resume ASC_EBCD_Exit
End Function

[Micron]

Ahh, OK. I presumed AMT was numeric. I looked up EBCDIC and had to wonder why anyone would have to deal with a problem today based on '60's tech.
You learn something every day. I can stop thinking now.

[Clint!]

Thanks Orange,

Yes, my list is as you have it.

"}JKLMNOPQR" ' equates to -0123456789

[Clint!]

Thank you, but we have figured this out.

[Clint!]

Thank you, I will give this a try.

[orange]

OK here is a slightly revised function. It does a little more checking.

'---------------------------------------------------------------------------------------
' Procedure : ASC_EBCD
' Author    : mellon
' Date      : 11-Oct-2017
' Purpose   : To convert a zoned ascii value to its ebcdic equivalent
'
'Parm:  strIn  --  a string of digits with optional leading sign
'
' If strIn has a leftmost "-", it is negative, so use the arrNeg array to resolve the Ebcdic
' If strIn has a leftmost " "  or "+", it is positive, so use the arrPos array to resolve the Ebcdic
'---------------------------------------------------------------------------------------
'
Function ASC_EBCD(strIN As Variant) As String

    Dim arrPos As String
    Dim arrNeg As String
    Dim ChrToChange As String

10  On Error GoTo ASC_EBCD_Error
20  If Not IsNumeric(strIN) Then
30      err.Raise 5555, , "Non numeric in Parameter"
40  End If
50  If Len(strIN) = 0 Then GoTo ASC_EBCD_Error
60  strIN = Trim(strIN)      'remove any leading/trailing spaces
70  ChrToChange = Right(strIN, 1) + 1
80  arrPos = "{ABCDEFGHI"    ' equates to +0123456789
90  arrNeg = "}JKLMNOPQR"    ' equates to -0123456789

100 If Left(strIN, 1) = "-" Then     'we're dealing with negative numbers
110     ASC_EBCD = Left(strIN, Len(strIN) - 1) & Mid(arrNeg, ChrToChange, 1)
120 ElseIf Left(strIN, 1) = "+" Then  'there is a plus sign, remove it
130     strIN = Mid(strIN, 2)
140     ASC_EBCD = Left(strIN, Len(strIN) - 1) & Mid(arrPos, ChrToChange, 1)
150 Else                                 'no sign involved
160     ASC_EBCD = Left(strIN, Len(strIN) - 1) & Mid(arrPos, ChrToChange, 1)
170 End If
    'Debug.Print ASC_EBCD            'for debugging
ASC_EBCD_Exit:
180 Exit Function

ASC_EBCD_Error:
190 If err.number = 424 Then
200     MsgBox "Invalid or No parameter was input" & " in line " & Erl & " (" & err.Description & ") in procedure ASC_EBCD of Module AWF_Related"
210 ElseIf err.number = 0 Then
220     MsgBox "Bad Parameter " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure ASC_EBCD of Module AWF_Related"
230 ElseIf err.number = 5555 Then
240     MsgBox "Bad Parameter " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure ASC_EBCD of Module AWF_Related"
250 Else
260     MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure ASC_EBCD of Module AWF_Related"
270     Resume ASC_EBCD_Exit
280 End If
End Function