Dsum Calculations

[Spook]

I have used a dsum to add up some fields in an access query. However
despite the field I'm adding up being currency I cannot get the result
to show as currency and the properties box of the dsum calculation
doesn't allow me to alter the format either.

What am I doing wrong???

Stephen

 

[RoyVidar]

Spook wrote :

I have used a dsum to add up some fields in an access query. However
despite the field I'm adding up being currency I cannot get the result
to show as currency and the properties box of the dsum calculation
doesn't allow me to alter the format either.

What am I doing wrong???

Stephen

Hi!

Just did a quick test, and to me it seems usage of DSum within the
query
returns something Access interprets as a string. A guick and dirty
workaround
could be to multiply with 1;

MySum: dsum("field","table","criterion = " & somefield) * 1

which gave numeric return that could be formatted on my setup.

There should probably be better solutions around, though...

Repeated calls to one of the domain aggregates within a query might be
a bit
hard on the recourses, you might perhaps try using a subquery in stead?

air code sample

select a.field1, a.field2,
(select sum(b.field3)
from someothertable b
where b.field1 = a.field1) as mysum
from sometable a
where...

 

[Spook]

Cheers for that. It worked perfectly.

Stephen

 

[Klatuu]

format(dsum("[myfield]","mytable","mycriteria"),"currency")