Access 2010 sql заявка за форматиране на финансови данни от 14 знака

Имам необработени финансови текстови файлове, които импортирам в Access 2010 и експортирам във формат на Excel. Тези файлове съдържат няколко полета с дължина от 14 символа, които представляват стойности в долари. Имам проблеми с конвертирането на тези полета във валута поради 14-ия знак. 14-ият знак е число, представено със скоба или буква. Той също така диктува дали уникалното поле е положителна или отрицателна стойност.

Положителните числа от 0 до 9 започват с отворена скоба { е нула, A е едно, B е две,...I е девет.
Отрицателни числа от -0 до -9 (знам, че -0 е математическа грешка, но останете с мен. Не знам как иначе да го обясня.) започват със затваряща скоба } е -0, J е -1, K е -2,...R е -9.

Примерни данни (всички принадлежат към едно и също поле/колона):
0000000003422{ преобразувано е $342,20
0000000006245} преобразувано е -$624,50
0000000000210N преобразувано е -$21,05
0000000011468D преобразувано е $1,146.84

Ето заявката, с която работя. Всеки път, когато го изпълня, цялото поле се изтрива. Бих предпочел да се придържам към SQL заявка, ако е възможно, но съм отворен за всички методи за разрешаване.

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")

ms-access-2010
person SpeakerBoxV    schedule 04.02.2015    source източник
comment
Струва ми се, че оценявате грешния характер. Mid("0000000000210N", 13, 1) връща 0. Mid("0000000000210N", 14, 1) връща N, което според мен всъщност искате. Забележка Right("0000000000210N", 1) ще ви даде N.   -  person HansUp    schedule 05.02.2015
comment
Разгледайте функцията Switch като алтернатива на множество вложени IIf() изрази   -  person HansUp    schedule 05.02.2015
comment
Благодаря на @HansUp за вашия принос. Оценявам го.   -  person SpeakerBoxV    schedule 06.02.2015

Отговори (1)


arrow_upward
1
arrow_downward

ето функция, която можете да извикате, за да конвертирате входен низ като тези във вашия пример в низ, форматиран по ваше желание.

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

Ако трябва да имате валута като върната стойност, можете да промените върнатия тип от String на Currency и да върнете съдържанието на променливата curConverted.

Чао.

person Wiz    schedule 05.02.2015