Solved

Rounding and storing to 2 decimal places in Access

Posted on 2012-04-11
7
329 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

717 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