DSum / Sum format

[Guest]

In Access query, I have two fields calculating running sum using DSum
function. In the third field I try to add the first two fields together. For
example,

field 1: DSum(...) = 123
field 2: DSum(...) = 123
field 3: [field 1] + [field 2] = 123123 (instead of 246)

Field 1 and field 2 are both general numbers. I don't know how to fix it.
Could anyone hlep me on this? Thanks.

 

[Allen Browne]

This is quite typical in JET. It doesn't understand whether the fields are
text (to be concatenated) or numeric (to be summed.)

The solution is to explicitly typecast. For example, if you are working with
Currency:
field1: CCur(Nz(DSum(...),0))
Use CLng() for whole numbers, or CDbl() for fractional numbers.

More info:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

 

[Guest]

Thank you very much, Allen. This is very helpful.

This is quite typical in JET. It doesn't understand whether the fields are
text (to be concatenated) or numeric (to be summed.)

The solution is to explicitly typecast. For example, if you are working with
Currency:
field1: CCur(Nz(DSum(...),0))
Use CLng() for whole numbers, or CDbl() for fractional numbers.

More info:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

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

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

In Access query, I have two fields calculating running sum using DSum
function. In the third field I try to add the first two fields together.
For
example,

field 1: DSum(...) = 123
field 2: DSum(...) = 123
field 3: [field 1] + [field 2] = 123123 (instead of 246)

Field 1 and field 2 are both general numbers. I don't know how to fix it.
Could anyone hlep me on this? Thanks.