Find/replace within a field multiple times...? 1

[CaptainObvious]

Ok, within a single text field called:
TYPE_FIELD
containing the string "A,B,C..."

How to replace
A with 1
B with 2
C with 3

so that the string becomes "1,2,3..."

The records will contain different combinations of the same codes (e.g. "A,C" "B,C" become "1,3" and "2,3" respectively)

TIA

 

[dawnd3]

I would use the find/replace feature and make sure it is just looking in that field. Dawn

http://www.calliopeconsulting.net

 

[tlbroadbent]

Here is a function that should work in a query or module.

Public Function ReplaceABC(sInput As String) As String
Dim pos As Integer
For pos = 1 To Len(sInput)
If Mid(sInput, pos, 1) = "A" Then
Mid(sInput, pos, 1) = "1"
ElseIf Mid(sInput, pos, 1) = "B" Then
Mid(sInput, pos, 1) = "2"
ElseIf Mid(sInput, pos, 1) = "C" Then
Mid(sInput, pos, 1) = "3"
End If
Next pos
ReplaceABC = sInput
End Function


Example: Use in a query

Select ReplaceABC(Type_Field) AS NewType
From TableName

Example: Change value in a text box.

Me.txtTypeField = ReplaceABC(Me.txtTypeField)

In Access 2000, you could use a nested execution of the Replace function.

?Replace(Replace(Replace("a,b,c","a","1&quot,"b","2&quot,"c","3&quot Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[MichaelRed]

While I appreciate Terry's nested Replacements, I fear that the 'average' reader of these Forums will get confused -even at the level of three. If the generic replacement is intended for the entire alphabet, I'm almost certain that most will NOT get through it (properly). Further, I would expect the process to become rather time intensive. Hence my somewhat more verbose and (hopefully) easier to implement and understand:

Public Function basAlph2Ord(strIn As String) As String

    'Replace characters with their Alphabetically Ordinal Position
    'Usage Example:

    '? basAlph2Ord("A,B,C...")
    '1,2,3...

    '? basAlph2Ord("A,b,C,4,X,X...")
    '1,2,3,4,24,24...


    'Michael Red    11/21/2001

    Dim Idx As Integer              'Loop Index
    Dim strOut As String            'Temp for output
    Dim MyStr As String             'Forced Conversion to UC
    Dim MyChr As String             'Character to Examine

    MyStr = UCase(strIn)            'Force Str to U.C., for Compare

    Idx = 1                         'Init Chr Index
    Do While Idx <= Len(strIn)      'Loop for String

        MyChr = Mid(MyStr, Idx, 1)                      'Get Single Char
        If (MyChr >= &quot;A&quot; And MyChr <= &quot;Z&quot;) Then         'Check U.C. &quot;A&quot; through &quot;Z&quot;
            strOut = strOut & Trim(Asc(MyChr) - 64)  'Force Letters
         Else
            strOut = strOut & MyChr                     'Keep # & Punctiation
        End If
        Idx = Idx + 1                                   'Incr Index
    Loop

    basAlph2Ord = strOut            'Return / Output

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[tlbroadbent]

Excellent reply Michael. I was too simplistic and wrong in assuming there were only three combinations possible. I took the example too literally without noticing the ... after the A,B,C. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.