Sum function truncates results

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a that has two decimal values, PCT and MAX_VAL, both of which are set
to have 2 decimal places. I have created a view (ACK! I mean "query" in
Access-speak!), which has a calculated value PCT_VAL which is [PCT] *
[MAX_VAL]. I have set the format of this column to be FIXED. I then created
a form, in which I want a total for all records displayed. I created a text
box (with the format set to FIXED, 2 decimal places) and used the formula
"=SUM([PCT_VAL]). The formula "works" (that is to say, a value is returned),
however it is truncated and then displayed with 2 decimal places. For
example, the record set has two values 2.34 and 2.5. The total should be
4.84, but it displayes 4.00. What could possibly be causing the data to be
truncated? I have set everything to be Decimal / Fixed with 2 decimal
places. Any thoughts?
 
Toby Grande said:
I have a that has two decimal values, PCT and MAX_VAL, both of which
are set to have 2 decimal places. I have created a view (ACK! I
mean "query" in Access-speak!), which has a calculated value PCT_VAL
which is [PCT] * [MAX_VAL]. I have set the format of this column to
be FIXED. I then created a form, in which I want a total for all
records displayed. I created a text box (with the format set to
FIXED, 2 decimal places) and used the formula "=SUM([PCT_VAL]). The
formula "works" (that is to say, a value is returned), however it is
truncated and then displayed with 2 decimal places. For example, the
record set has two values 2.34 and 2.5. The total should be
4.84, but it displayes 4.00. What could possibly be causing the data
to be truncated? I have set everything to be Decimal / Fixed with 2
decimal places. Any thoughts?
Click to expand...

What version of Access?

You say these are decimal fields? In the table design, what are the
fields' values for each of these properties:

Data Type
Field Size
Format
Precision
Scale
Decimal Places

?
 
Access 2000.
Datatype = "Number"
Field Size = "Decimal"
Precision = 5
Scale = 2
Decimal Places = 2

For reference, when I changed the fields to "Single" instead of "Decimal" it
stopped truncating the numbers in the SUM function. It would seem that the
SUM function doesn't handle "Decimal" appropriately. I noticed that another
user reported the same circumstances in creating a pivot table.
 
Toby Grande said:
Access 2000.
Datatype = "Number"
Field Size = "Decimal"
Precision = 5
Scale = 2
Decimal Places = 2

For reference, when I changed the fields to "Single" instead of
"Decimal" it stopped truncating the numbers in the SUM function. It
would seem that the SUM function doesn't handle "Decimal"
appropriately. I noticed that another user reported the same
circumstances in creating a pivot table.
Click to expand...

I think you are probably right. I know that there have been some
problems with the handling of the Decimal data type, which was a
relatively recent addition, but I can't remember any details offhand.
The first thing I'd recommend is that you make sure you have the latest
version of Jet, which should be SP 8. I haven't yet been able to
reproduce your problem with the fully updated version of Access 2002
that I'm using, so it may be that updating Jet will solve it.

If that doesn't correct the problem, then if you need decimal precision,
I suggest you use the Currency data type instead. That's a precise,
scaled integer type with 4 decimal places, so it's not subject to the
imprecision of the Single and Double floating-point types. You just
have to set the Format propery of the fields so that they don't come out
looking like dollars and cents.

Of course, if you don't actually need that precision, you can use Double
or Single fields with marginally less hassle.
 
Back
Top