Solved

Access report drops decimal places from field in query

Posted on 2011-02-25
10
691 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
  • 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 119

Expert Comment

by:Rey Obrero
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
 
LVL 119

Expert Comment

by:Rey Obrero
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
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.

706 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now