Access report drops decimal places from field in query

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".
popthedotAsked:
Who is Participating?
 
popthedotConnect With a Mentor Author Commented:
I made this change but I'm still getting the same result.  I can't change anything in the oracle database.
0
 
Patrick MatthewsConnect With a Mentor Commented:
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
 
Rey Obrero (Capricorn1)Commented:
try

formatnumber([field]/100,2)

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Rey Obrero (Capricorn1)Commented:
what change did you do?
0
 
Jeffrey CoachmanMIS LiasonCommented:
<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
 
Rey Obrero (Capricorn1)Commented:
wasn't the post at http:#a34981051 suggested the format?
0
 
Patrick MatthewsCommented:
Rey,

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

:)

Patrick
0
 
Rey Obrero (Capricorn1)Commented:
that is not what i can read

I added format([field]/100.0)"0.00").
0
 
Patrick MatthewsCommented:
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
 
popthedotAuthor Commented:
I added format([field]/100.0)"0.00").
Thanks for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.