Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Join Date
    Jan 2007
    Posts
    18

    Unanswered: Mainframe ASCII File Format

    Hey everyone,

    I have a huge challenge. A client of mine wants me to update his data so it is in Mainframe ASCII File Format. That means no special characters like decimals points or negative signs.

    It's all going well till I get to a field that contains (currency) with negative values. Since I have to convert to this special format I have to go through many different steps that I'm not quite sure how to do.

    I need to indicate a negative sign with the appropriate alpha character. I replace this character with the very last character, depending what the last place character is.

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

    Example1: DATA SHOWS -234.00
    ASCII FORMAT 2340}
    Example2:
    DATA SHOWS -34.93
    ASCII FORMAT 349L

    So somehow I need to identify the last place character only with it’s a negative value, make it a positive value, and then replace the last character with its appropriate alpha character.

    THANK YOU all so much in advance. Sorry it's so long but I wanted to be descriptive. If you need more info let me know.

    PS. Sometimes the values are whole numbers (not converted over to currency format (-100) ) so I then convert it, pull the dollars signs and decimals out, but I get stuck with the brackets recognizing it as a negative.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Flag the value as negative (a conditional).
    ABS the value.
    Take the RIGHTmost digit.
    Run it thru your case statement
    Concat the value - the last digit with the replacement token
    REPLACE any periods with empty strings

    All done
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jan 2007
    Posts
    18
    hmm... im sorrry i forgot to mention that im kinda new to access., i have never wrote a IF statement.
    I do know how to find the right most digit, and remove periods but thats amount all from what you mentioned.
    Ill keep doing some research but thank you for your help.

  4. #4
    Join Date
    Jan 2007
    Posts
    7
    I also need to know how to flag a negative and ABS the value.

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    I see ... Programming 101 then. BTW, Poots is gonna harangue me for this ... As I don't teach how to program ...

    Flagging: Make a Boolean variable that you set to true WHEN the value is negative. This requires the use of a conditional ... (if you do not know what a conditional is, try looking in the Help for IF THEN ELSE ENDIF)

    ABS'ing: ABS is a VB function you call that returns the ABSOLUTE value of the input supplied to it. Again, check out the Help ...
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by M Owen
    BTW, Poots is gonna harangue me for this ... As I don't teach how to program ...
    . harangue
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - if this is to be done in a query then check out the Iif function (expansion on Mike's suggestion re conditional statements)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2007
    Posts
    18
    ok thanks alot, ill check it out, and get back to ya'll

  9. #9
    Join Date
    Jan 2007
    Posts
    18
    Hey, im back with my select case function..I am unable to get my code off and going though. So i was hoping someone that is good with this stuff can look over it for me please...

    Public Function NoNeg()

    Dim splitVal

    If InitVal < 0 Then
    splitVal = Split(Str(Abs(MyNumber * 10)), ".")
    End If


    Select Case splitVal(1)
    Case Right(CStr(YourNumber,1) = 0
    newNumber = Replace(Left(CStr(YourNumber), Len(CStr(YourNumber)) - 1) & "}", ".", "")

    'then continue thru -9

    End Select
    End If

    End Function

  10. #10
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Yikes! WTF is this stuff? Why Split?

    All you need to do is :

    Dim MyBool as boolean, MyStr as string,Mytoken as string

    If MyNum < 0 then Mybool=true
    mystr=str(abs(mynum))
    mystr=replace(mystr,".","")
    mytoken=right(mystr,1)

    mystr=left(mystr,len(mystr)-1)

    SELECT CASE mytoken
    CASE "0"
    mystr=mystr & "}"
    CASE "1"
    mystr=mystr & "J"
    CASE "2"
    ...
    END SELECT

    Got it?
    Back to Access ... ADO is not the way to go for speed ...

  11. #11
    Join Date
    Jan 2007
    Posts
    18
    yah-you cant tell, im very new to this and im a blonde...haha

    ill try it out, thanks mike

  12. #12
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Correction:

    if mybool then
    mystr=left(mystr,len(mystr)-1)

    SELECT CASE ...

    ...

    END SELECT

    endif

    Otherwise, you're setting EVERY value to negative ...
    Back to Access ... ADO is not the way to go for speed ...

  13. #13
    Join Date
    Jan 2007
    Posts
    18
    awesome, everything works great except one major issue..

    the computer automatically formats a number with ending zeros.

    example (computer results):

    -400.00 === 400 === 40}

    or

    -310.10 === 310.1 === 310J

    while it should 4000} and 3101}

    any ideas?

  14. #14
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Insert a Format into your sequence ... Ex:

    Dim MyBool as boolean, MyStr as string,Mytoken as string

    If MyNum < 0 then Mybool=true
    mystr=FORMAT(abs(mynum),"#,##0.00")
    if mybool then
    mystr=replace(mystr,".","")
    mytoken=right(mystr,1)

    mystr=left(mystr,len(mystr)-1)

    SELECT CASE mytoken
    CASE "0"
    mystr=mystr & "}"
    CASE "1"
    mystr=mystr & "J"
    CASE "2"
    ...
    END SELECT
    endif

    In this case, replace the STR call with the FORMAT call ...
    Back to Access ... ADO is not the way to go for speed ...

  15. #15
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Per your PM:

    MyNum is the number that you want to format ... Whatever it is or is called ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •