GIStewart
asked on
Rounding and storing to 2 decimal places in Access
I am being sent a fixed length file which I'm importing into Access 2003, and then converting the data to compare against another table where the values are stored to 2 decimal places in USD.
FldA is an example of a value in the fixed length file, and its cobol format is PIC 9(9)v9(6). This value is in local currency so it needs to be translated to USD, and then rounded and stored to the same 2 decimal places. For the rounding, 5 and higher should be rounded up, 4 and lower should be rounded down.
This is as far as I got: [FldA]/1000000*FXRate
The rounding and storing to 2 decimal places is stumping me...
Thank you.
FldA is an example of a value in the fixed length file, and its cobol format is PIC 9(9)v9(6). This value is in local currency so it needs to be translated to USD, and then rounded and stored to the same 2 decimal places. For the rounding, 5 and higher should be rounded up, 4 and lower should be rounded down.
This is as far as I got: [FldA]/1000000*FXRate
The rounding and storing to 2 decimal places is stumping me...
Thank you.
http://allenbrowne.com/round.html
ASKER
That was 1 of the options I tried already, but it doesn't seem to store the value to 2 decimal places. On my comparison table, 150 comes over as 150 but I need 150.00
in the database if you change the format of the field that is storing this number .. it should work....
ASKER
Unfortunately, it didn't.
I also tried:
Round(100 * [MyField], 0) / 100
from Allen Browne's site, but again it didn't store the value to 2 decimal places.
I also tried:
Round(100 * [MyField], 0) / 100
from Allen Browne's site, but again it didn't store the value to 2 decimal places.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
gustav - thank you, works perfectly
You are welcome!
/gustav
/gustav