Access 2010 sql query to format 14 character finance data

Question

I have raw finance text files that I'm importing into Access 2010 and exporting in Excel format. These files contain several 14 character length fields which represent dollar values. I'm having issues converting these fields into currency because of the 14th character. The 14th character is a number represented by a bracket or letter. It also dictates whether the unique field is a positive or negative value.

Positive numbers 0 to 9 start with open bracket { being zero, A being one, B being two,...I being nine.
Negative numbers -0 to -9 (I know, -0 is a mathematical faux pas but stay with me. I don't know how else to explain it.) start with close bracket } being -0, J being -1,K being -2,...R being -9.

Example data (all belonging to the same field/column):
0000000003422{ converted is $342.20
0000000006245} converted is -$624.50
0000000000210N converted is -$21.05
0000000011468D converted is $1,146.84

Here's the query that I'm working with. Each time I execute it, the entire field is deleted though. I would prefer to stick to a SQL query if possible but I'm open to all methods of resolution.

SET FIELD_1 = Format(Left([FIELD_1],12) & "." & Mid([FIELD_1],13,1) & IIf(Right([FIELD_1],1)="{",0,IIf(Right([FIELD_1],1)="A",1,IIf(Right([FIELD_1],1)="B",2,IIf(Right([FIELD_1],1)="C",3,IIf(Right([FIELD_1],1)="D",4,IIf(Right([FIELD_1],1)="E",5,IIf(Right([FIELD_1],1)="F",6,IIf(Right([FIELD_1],1)="G",7,IIf(Right([FIELD_1],1)="H",8,IIf(Right([FIELD_1],1)="I",9,"")))))))))),"$##0.00"), IIf(Right([FIELD_1],1)="}",0,IIf(Right([FIELD_1],1)="J",1,IIf(Right([FIELD_1],1)="K",2,IIf(Right([FIELD_1],1)="L",3,IIf(Right([FIELD_1],1)="M",4,IIf(Right([FIELD_1],1)="N",5,IIf(Right([FIELD_1],1)="O",6,IIf(Right([FIELD_1],1)="P",7,IIf(Right([FIELD_1],1)="Q",8,IIf(Right([FIELD_1],1)="R",9,"")))))))))),"-$##0.00")

Seems to me you're evaluating the wrong character. `Mid("0000000000210N", 13, 1)` returns *0*. `Mid("0000000000210N", 14, 1)` returns *N*, which is what I think you actually want. Note `Right("0000000000210N", 1)` will give you *N*. — HansUp, Feb 04 '15 at 22:08
[Consider the Switch Function as an alternative to multiple nested IIf() expressions](http://stackoverflow.com/a/15776608/77335) — HansUp, Feb 04 '15 at 22:11

score 1 · Answer 1 · answered Feb 05 '15 at 11:37

here is a function that you can call to convert an input string like the ones in your example into a string formatted as you desire.

Private Function ConvertCurrency(strCur As String) As String
    Const DIGITS = "{ABCDEFGHI}JKLMNOPQR"

    Dim strAlphaDgt As String
    Dim intDgt As Integer, intSign As Integer
    Dim f As Integer
    Dim curConverted As Currency

    strAlphaDgt = Right(strCur, 1)               ' Extract 1st char from right

    f = InStr(DIGITS, strAlphaDgt)               ' Search char in DIGITS. Its position is related to digit value

    intDgt = (f - 1) Mod 10                      ' Converts position into value of the digit

    intSign = 1 - 2 * Int((f - 1) / 10)          ' If it's in the 1st half is positive, if in the 2nd half of DIGITS it's negative

    curConverted = intSign * _
        CCur(Left(strCur, Len(strCur) - 1) & _
        Chr(intDgt + 48)) / 100                  ' Rebuild a currency value with 2 decimal digits

    ConvertCurrency = Format(curConverted, _
        "$#,###.00")                             ' Format output

End Function

If you need to have a Currency as returned value, you can change the type returned from String to Currency and return the content of curConverted variable.

Bye.

Access 2010 sql query to format 14 character finance data

1 Answers1