Solved

Access report drops decimal places from field in query

Posted on 2011-02-25
10
697 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
Industry Leaders: 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!

 
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

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.

Question has a verified solution.

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

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

739 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