Results 1 to 5 of 5
  1. #1
    BrianF is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2011
    Posts
    6

    Add decimal in position

    I need to import data from a text file. Two of the fields are currency amounts. However, the last two characters of these two fields are the cents and I need Access to add a decimal in that spot. That makes a difference between $1.00 and $100. The file (attached) is a fixed width file from our in house software.

    The fields are:
    FIELD START FORMAT
    ACCTNUM 1 X(17)
    PAYREF 18 X(30)


    POSTDATE 48 YYYYMMDD
    DRAMT 56 999999999
    CRAMT 65 999999999

    The last two digits of the CRAMT and DRAMT are the "cents" of the amts. All amounts are (+) in value. Example: 000015025= $150.25

    How can I get access to add this decimal into the correct position? I have tried the "Advanced" option, but it is still importing 000015025 as $15,025.00.
    Attached Files Attached Files

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,684
    I see two methods:

    1) Write VBA to read each line, parse each value to a variable, delete/remove/divide to get the amount to what you want

    2) Import to a temp table, run an update query to divide the field "DRAMT" by 100 (or convert to currency using the CCur() function), then run an append query to move the data from the temp table to the "real" table.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    I agree with ssnafu to an extent but would like to offer another solution (I don't know what your end product with this data is for so you can take this with a grain of salt). I am not a huge fan of temp tables so you can import your data into your 'main' data set but have a 'processingflag' (a yes/no field that is default set to no (0)) Then run an update query that divides your imported (processing flag = 0) data by 100 and sets the processing flag to -1 (yes)). Then this becomes a simple 2 step process that you can do all with queries and not involve vba/temp tables.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,684
    I agree with ssnafu to an extent but
    That's ssanfu, but sometimes I am s-snafu 'ed up

    My reasoning:
    It seems that this is for a business, so I error on the side of caution. Using VBA/temp tables (NOT make tables), I feel I have more control over the process and error handling.
    A couple of my projects was strictly VBA processing of text/CSV files; more than 3 fields needed to be "cleaned up" and/or edited/changed.

    In the example text file the OP provided (thanks BTW), the 2nd to the last line (beginning '999991') is munged. I had to change the font in the text file to "Fixedsys", then adjust the columns to be able to import the file correctly.

    I am more comfortable using a temp table so I can check the data before the final import into the 'main' table.


    Note that I have used a similar method as your proposal for some of my non-critical personal projects. And it works quite well.
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,440
    I agree with you 100% in that it really depends on the final product and how sensitive the data is or how easy it would be to reconstruct the data. I'm just offering suggestions to complement yours not as a substitute

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Decimal Places
    By momodoujimnjie in forum Access
    Replies: 1
    Last Post: 01-09-2013, 04:49 AM
  2. Position of scroll bar
    By VictoriaAlbert in forum Access
    Replies: 2
    Last Post: 04-14-2011, 04:29 PM
  3. Set Form Position
    By Yance in forum Programming
    Replies: 3
    Last Post: 11-29-2010, 02:20 PM
  4. Need Currency or Decimal Help!
    By Jaricketts in forum Access
    Replies: 2
    Last Post: 08-19-2010, 09:39 PM
  5. Converting HH:MM to decimal
    By katrinanyc926 in forum Queries
    Replies: 5
    Last Post: 08-13-2010, 02:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums