Solved

Rounding and storing to 2 decimal places in Access

Posted on 2012-04-11
7
324 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

910 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

23 Experts available now in Live!

Get 1:1 Help Now