Solved

Access report drops decimal places from field in query

Posted on 2011-02-25
10
698 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 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 500 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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 92

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 92

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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…
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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

705 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