Solved

Rounding and storing to 2 decimal places in Access

Posted on 2012-04-11
7
328 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 50

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 50

Expert Comment

by:Gustav Brock
ID: 37832937
You are welcome!

/gustav
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

738 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