incorrect sum result

  • Thread starter Thread starter Mike Thompson
  • Start date Start date
M

Mike Thompson

I used a sum() function in the footer to sum a simple
column of numbers. The total shoud clearly be 4.10 but the
field displayes 4.00. Any ideas?
 
I used a sum() function in the footer to sum a simple
column of numbers. The total shoud clearly be 4.10 but the
field displayes 4.00. Any ideas?
Click to expand...

My guess is that there's a Long Integer (the default Number datatype)
involved somehow. What's the datatype of the field that you're
summing? What is the exact Sum expression? And what are the Control
Source (if other than the expression itself), Format and Decimal
Places properties of the textbox in the footer?
 
Thanks for reply.
1. The field is derived from a query that points to a
decimal 22,6 type field.
2. =Sum([TotActHrs])
3. fixed with 2 places.
 
Thanks for reply.
1. The field is derived from a query that points to a
decimal 22,6 type field.
2. =Sum([TotActHrs])
3. fixed with 2 places.
Click to expand...

Is the underlying table in SQL/Server (MSDE) or a JET (.mdb file)
database? The Decimal datatype is rather new to Access though it's old
hat in SQL; I'm not sure whether this might be at fault!
 
It's an mdb. I'm doing the same type of summing in other
forms and they work fine. Maybe I just need to rebuild
this form. I apreciate your help.
-----Original Message-----
Thanks for reply.
1. The field is derived from a query that points to a
decimal 22,6 type field.
2. =Sum([TotActHrs])
3. fixed with 2 places.
Click to expand...

Is the underlying table in SQL/Server (MSDE) or a JET (.mdb file)
database? The Decimal datatype is rather new to Access though it's old
hat in SQL; I'm not sure whether this might be at fault!



.
Click to expand...
 
Ask the query how it understands the data.
Open the Immediate window (ctrl+G) and enter:

? TypeName(DLookup("TotActHrs", "NameOfYourQueryHere"))
? TypeName(DSum("TotActHrs", "NameOfYourQueryHere"))

If that replies with Decimal in both cases, then, yes, the problem is with
the form. If it does not return Decimal and this is a calculated field in
the query, try wrapping the calculation in CDbl() or possibly CDec().

Forcing the type to a double may solve the problem anyway.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

It's an mdb. I'm doing the same type of summing in other
forms and they work fine. Maybe I just need to rebuild
this form. I apreciate your help.
-----Original Message-----
Thanks for reply.
1. The field is derived from a query that points to a
decimal 22,6 type field.
2. =Sum([TotActHrs])
3. fixed with 2 places.
Click to expand...

Is the underlying table in SQL/Server (MSDE) or a JET (.mdb file)
database? The Decimal datatype is rather new to Access though it's old
hat in SQL; I'm not sure whether this might be at fault!
Click to expand...
Click to expand...
 
Back
Top