incorrect sum result

[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?

 

[John Vinson]

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?

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?

 

[Guest]

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.

 

[John Vinson]

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.

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!

 

[Guest]

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.

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!



.

 

[Allen Browne]

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.

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!