Solved

Rounding and storing to 2 decimal places in Access

Posted on 2012-04-11
7
326 Views
Last Modified: 2012-04-11
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.
0
Comment
Question by:GIStewart
  • 3
  • 2
  • 2
7 Comments
 
LVL 17

Expert Comment

by:Anuroopsundd
ID: 37832576
0
 

Author Comment

by:GIStewart
ID: 37832589
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
0
 
LVL 17

Expert Comment

by:Anuroopsundd
ID: 37832597
in the database if you change the format of the field that is storing this number .. it should work....
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:GIStewart
ID: 37832652
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.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 37832662
Format is for this, and it does a perfect 4/5 rounding:

RateRounded: Format([FldA] / 1000000 * FXRatev, "0.00")

/gustav
0
 

Author Comment

by:GIStewart
ID: 37832676
gustav - thank you, works perfectly
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 37832937
You are welcome!

/gustav
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Documents and settings folder 30 75
Access report groups with sums 5 24
unable to create table-based data macro in MS ACCESS 2013 11 22
aggregate query? 3 27
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now