Add decimal in position

[BrianF]

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.

[ssanfu]

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.

[rpeare]

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.

[ssanfu]

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.

[rpeare]

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