Multiple replace functions in a single query field (1 Viewer)

[chipcain]

I have been asked to convert an alphanumeric field where the alph character represents a number and identifies the whole field as a negative number. I'll try explain by example. Colum one below is the original alpha numeric field and the second colum is what the first clum repesents in numeric form. I have been able to do a simple replace function and divide the result by "-1" to get the negative number desired. See below

0000000}

-1​

0000001}

-10​

0000010}

-100​

My problem is that the data in column one has several alpha characters each representing a different number. See below the result I am after.

0000001}

-10​

0000001J

-11​

0000001K

-12​

The replacement function for the first example looks like this:

((Replace([Transmittal for 2007 Resubmission1]![Written_Exp],"}","0"))/(-1))

But I don't know how to put more than one replace funtion together. And to make matters worse there are records with no alpha characters which represent positive numbers: see an example below:

0000001}

-10​

00000010

10​

0000001K

-12​

Where this becomes a problem is when I divide by "-1" Obviously I only want to divide by "-1" when there is an alph character in the field I am converting.

I am hoping one or more of you have a solution as I have exhausted my limited knowledge and search avenues for a solution.

Thank you,
Chip

 

[vbaInet]

I have been asked to convert an alphanumeric field where the alph character represents a number and identifies the whole field as a negative number. I'll try explain by example. Colum one below is the original alpha numeric field and the second colum is what the first clum repesents in numeric form. I have been able to do a simple replace function and divide the result by "-1" to get the negative number desired. See below

0000000}

-1​

0000001}

-10​

0000010}

-100​

My problem is that the data in column one has several alpha characters each representing a different number. See below the result I am after.

0000001}

-10​

0000001J

-11​

0000001K

-12​

The replacement function for the first example looks like this:

((Replace([Transmittal for 2007 Resubmission1]![Written_Exp],"}","0"))/(-1))

But I don't know how to put more than one replace funtion together. And to make matters worse there are records with no alpha characters which represent positive numbers: see an example below:

0000001}

-10​

00000010

10​

0000001K

-12​

Where this becomes a problem is when I divide by "-1" Obviously I only want to divide by "-1" when there is an alph character in the field I am converting.

I am hoping one or more of you have a solution as I have exhausted my limited knowledge and search avenues for a solution.

Thank you,
Chip

Combine these functions IIF(), Replace(), IsNumeric() and Val(). Something like:

IIF(IsNumeric([Transmittal for 2007 Resubmission1]![Written_Exp]),,Val(Replace([Transmittal for 2007 Resubmission1]![Written_Exp],"}","0"))/(-1))

 

[dcb]

Create a function (you can then use it in a query)

Get the last char using Right(str,1)
Evaluate the char ---
If its a number then add it to the string after converting to number
Left(str,(Len(Str)-1)
If Text then use a IF or Case to evaluate to number
THen do as above * -1

 

[chipcain]

Combine these functions IIF(), Replace(), IsNumeric() and Val(). Something like:

IIF(IsNumeric([Transmittal for 2007 Resubmission1]![Written_Exp]),,Val(Replace([Transmittal for 2007 Resubmission1]![Written_Exp],"}","0"))/(-1))

This does take care of all my positive numbers and the negative numbers represented with the "}". But I still can't seem to put a string together so that this works where }=0, J=1, K=2, ectetera. If I use more than one replace function then I get errors in all the field; including the positive numbers.

 

[vbaInet]

Revised version:

IIF(IsNumeric([Transmittal for 2007 Resubmission1]![Written_Exp]),,Val(Left([Transmittal for 2007 Resubmission1]![Written_Exp],Len([Transmittal for 2007 Resubmission1]![Written_Exp]) - 1) & "0")/(-1))

However, if you can get your head round dcb's solution, it's more efficient.

 

[chipcain]

Create a function (you can then use it in a query)

Get the last char using Right(str,1)
Evaluate the char ---
If its a number then add it to the string after converting to number
Left(str,(Len(Str)-1)
If Text then use a IF or Case to evaluate to number
THen do as above * -1

Hi dcb, I have to be honest this confuses me. I'm not proficient with Access and am not a programmer so I'm not sure how to lay this out.

I'm embarassed to ask but can you provide an example of how you would expect it to look. Would you need something more from me?

 

[dcb]

Hi dcb, I have to be honest this confuses me. I'm not proficient with Access and am not a programmer so I'm not sure how to lay this out.

I'm embarassed to ask but can you provide an example of how you would expect it to look. Would you need something more from me?

I can do one for you -
I would need a list of the Neg Chars and what they relate too

 

[chipcain]

I can do one for you -
I would need a list of the Neg Chars and what they relate too

Here's the List:

}-0
J-1
K-2
L-3
M-4
N-5
O-6
P-7
Q-8
R-9

I have an 8 character text field with whole numbers plus numbers that end with one of the characters listed above. As an example:

Field1
00000010
0000001}
00000523
0000052R

Using a query I was creating a second field (Field2) to convert them to their proper numeric value. As an example: Unfortunately I don't know how to put in a table here.

Field1
00000010
0000001}
00000523
0000052R

Field2
10
-10
523
-529

I have 5 fields that need convered in a similar way and there are almost 300,000 records so there is no easy way other than using Access.

dcb, thank you for your time; any help you can provide will be greatly appreciated.

 

[vbaInet]

This sounds like a job for recordset. I will see if I can bash something out.

 

[vbaInet]

I've got something, haven't really tested it with much code. Before you use this function please backup and test on the backed up data.

Are the 5 fields in the same table? Starting with 0, reading from left to right, what are the numbers of the fields? 1 to 5 maybe?

 

[dcb]

Function: Place in std module

Public Function fNumTrans(AlphaN As String) As Long
'---------------------------------------------------------------------------------------
' Procedure : fNumTrans
' Author    : DCB
' Date      : 29/01/2010
' Purpose   : Special Number Translation
' Return    : Long
'---------------------------------------------------------------------------------------
'
    Dim intNeg As Integer
    Dim varIndicator As Variant
    Dim intType As Integer
    Dim intIndicator As Integer
    Dim intRemainder As Long
    
On Error GoTo fNumTrans_ERROR

        intNeg = 1
        varIndicator = Right(AlphaN, 1)
        intType = varType(varIndicator)
        intIndicator = varIndicator
        
        '' This will step out to a Type error if the last digit is string
fNumTrans_Cont:
    intRemainder = Left(AlphaN, (Len(AlphaN) - 1))
        fNumTrans = CInt(intRemainder & intIndicator) * intNeg
        GoTo fNumTrans_EXIT
fNumTrans_String:
    Select Case varIndicator
        Case "}": intNeg = -1: intIndicator = 0
        Case "J": intNeg = -1: intIndicator = 1
        Case "K": intNeg = -1: intIndicator = 2
        Case "L": intNeg = -1: intIndicator = 3
        Case "M": intNeg = -1: intIndicator = 4
        Case "N": intNeg = -1: intIndicator = 5
        Case "O": intNeg = -1: intIndicator = 6
        Case "P": intNeg = -1: intIndicator = 7
        Case "Q": intNeg = -1: intIndicator = 8
        Case "R": intNeg = -1: intIndicator = 9
        Case Else:    Err.Raise 54321, "fNumTrans", "The string input does not compute"
    End Select
    Debug.Print "Type is Neg", varIndicator & " = " & intIndicator

GoTo fNumTrans_Cont

   On Error GoTo 0

fNumTrans_EXIT:
    DoCmd.SetWarnings True
    
    Exit Function
    
fNumTrans_ERROR:
    ''Trap
    If Err.Number = 13 Then
    ''Trapped For 13 [Type mismatch]
    GoTo fNumTrans_String
    Else
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure fNumTrans of Module Module1"
    GoTo fNumTrans_EXIT
    End If
    
End Function

Usage:

SELECT YourTable.YourStringNumber, fNumTrans([YourStringNumber]) AS OP_Number
FROM YourTable;

Output:

YourStringNumber    OP_Number
00000010    10
0000001}    -10
00000523    523
0000052R    -529

Cheers

 

[vbaInet]

Good stuff dcb. I will post mine so the poster can play around with both solutions:

        Dim yRow As Long, xCol As Long, rstClone As Recordset
    
    Set rstClone = CurrentDb.OpenRecordset("NameOfTable").Clone
    
    With rstClone
        If .BOF = False Then
            If MsgBox("Backup your data before continuing" & vbCrLf & "Continue?", vbYesNo) = vbYes Then
                .MoveLast
                .MoveFirst
                For yRow = 0 To .RecordCount - 1
                    For xCol = 1 To 2
                        If IsNumeric(Right(.Fields(xCol), 1)) = False And (.Fields(xCol) & "") <> "" Then
                            .Edit
                            .Fields(xCol) = Left(.Fields(xCol), Len(.Fields(xCol)) - 1) & "0"
                            .Update
                        End If
                    Next
                    .MoveNext
                Next
                MsgBox "Finished updating."
            End If
        Else
            MsgBox "There are no records"
        End If
    End With
    rstClone.Close
    Set rstClone = Nothing

The line "For xCol = 1 to 2" represents the column index it should start from and finish at. Column index start from 0. Oh, and replace NameOfTable with the name of your table at the openrecordset line.

 

[chipcain]

I'm sorry to have waited so long to respond. I have been pulled off to another "emergency" But I need to thank both vbaInet & dcb for your help. I has indeed helped me with my problem.

Thank you,
Chip

 

[vbaInet]

Happy we could help.