?
Solved

Access report drops decimal places from field in query

Posted on 2011-02-25
10
Medium Priority
?
699 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

752 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