[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Access report drops decimal places from field in query

Posted on 2011-02-25
10
Medium Priority
?
703 Views
Last Modified: 2012-05-11
I am creating a query based on data from an Oracle database.  The field is defined as number in the database, and I do not have access to change it.  The oracle database stores numbers without the decimal places.  Value of $23.41 would be 2341.  I am calculating the amount by
[field]/100.  Works great in the query, but when I put the amount field on a report, it drops the decimal places.  If I change the format to currency, it just adds ".00".
0
Comment
Question by:popthedot
[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
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 2000 total points
ID: 34980800
Is the query you based the report on a pass-through?  If so, then dividing the column by 100 is going to result in integer division.

In SQL Server you could force an implicit data type conversion by dividing by 100.0 instead of simply 100; no idea if that works the same way in Oracle.  If not, you may need to use Oracle's type-conversion functions.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34981051
try

formatnumber([field]/100,2)

0
 

Accepted Solution

by:
popthedot earned 0 total points
ID: 34981086
I made this change but I'm still getting the same result.  I can't change anything in the oracle database.
0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34981118
what change did you do?
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 34981196
<I am calculating the amount by
[field]/100.  Works great in the query, but when I put the amount field on a report,>
Put what Amount Field in the report?
The query calculated field, or the raw "No decimals" field?

AFAIK, if you bring the Query calculated field into the report (as a Field) then the currency format should display it correctly.

I can see many ways that you may be able to work around this.

But consider matthewspatrick's post first and see if the raw data can be converted to the correct format in the source ...
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34981300
wasn't the post at http:#a34981051 suggested the format?
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 34981331
Rey,

It's a data type issue, not a formatting issue.

:)

Patrick
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 34981353
that is not what i can read

I added format([field]/100.0)"0.00").
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 34981684
My suspicion is that the Format expression is neither here nor there.  It is the division by 100.0 instead of by 100 that is making this work.
0
 

Author Closing Comment

by:popthedot
ID: 35015459
I added format([field]/100.0)"0.00").
Thanks for your help.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

650 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