Rounding Problem-Please Help!!!

  • Thread starter Thread starter Bonnie
  • Start date Start date
B

Bonnie

Hi there! Using A02 on XP. Have clients that submit data
in delimited files that I import to Access in order
to 'spit out' data in our format (comma delimited ascii).
One client sends data already in .csv format. Have 3
columns containing dollar amounts. Data may look like:
110.75,50,16.3,15,56.88, etc. Problem is, in Access my
grand totals are showing multi-digits like; 15,939.080 or
21,785.449. The checks are for 15,939.06 and 21,785.43. I
have played with the format in Excel and Access, before
and after the export/import but there are only 2 digits
(at the most) in any field. I've tried paste special, etc.
Any ideas??? How could this be happening? Why?

Thanks in advance for any help or advice!!!
 
Hi,

My name is Amy Vargo. Thank you for using the Microsoft Access Newsgroups.

It sounds like you need to develop a function to round or truncate currency
values to the intended number of decimal places. The Format property of a
control can round a Number or Currency field to the number of decimal
places that you want. However, this does not change the underlying data,
which may contain additional digits that the control does not display. If
you add the values in this control, the sum is based on the actual values
and not on the displayed values. This behavior may make the total seem
inaccurate.

This article demonstrates how to create two user-defined functions to
truncate data to two decimal places so that the displayed and
formatted value and the actual numeric or currency data are the same. It
also shows you how to use the built-in Round() function to round the data
to the intended number of decimal places.

210564 ACC2000: Round or Truncate Currency Values to the Intended Number of
http://support.microsoft.com/?id=210564


I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Amy Vargo
Microsoft Access Engineer


This posting is provided 'AS IS' with no warranties, and confers no rights.
You assume all risk for your use. © 2001 Microsoft Corporation. All rights
reserved.
 
Good morning, Amy! Thanks for the advice; however my
situation won't quite fit that solution. My data cannot be
currency as I am working with comma delimited files to
load to our system and the contributions cannot contain
the $ symbol. So, I am using Number/Single for size and
auto on digits. Even if I set for 2 digits in the table,
my total query shows the weird total. I can set for more
digits but none of the numbers in the table will show more
than 2 digits, some have one, others none; still the weird
total 3590.0800225. Driving me nuts! I need to apply the
truncate with a query to perhaps populate a new field with
only 2 digits and nothing more. Or must I do as the
article advises and run my records through the form and
truncate individually?

Thanks again for your prompt assistance.
 
Back
Top