Acess 2000 report with multiple sum values

T

Todd Spear

I have a report that has 32 entries bound to a current table. I need to sun
all of thesse values basd on a where clause. I Tries DSUM function but
apparently there is only 255 characters allowed per DSUM string?

In good database design - totals are NOT stored in tables but I do not know
how to solve this problem?

Is there a way to set report values -- mostly textboxes - via code and then
print. I have a form that collects the where clause parameters.

Thanks in advance

Todd
 
A

Allen Browne

Todd there are multiple ways to approach this issue.

If may be possible to create an expression in the group footer, such as:
=Sum(IIf([Field1]= 2, [Field3], 0))
That should sum the value of Field3, but only for those records where Field1
equals 2.

Another aproach is place a subreport in the Report Footer section, and make
this subreport show the totals.

Regarding DSum(), I am not aware of a 255-char limit on the Criteria string.
Perhaps the limit applies to the Builder, and not on the function? If there
is a limit, you could create your own replacement function for DSum() that
allows more. As an example, this link shows how to make a replacement for
DLookup():
http://allenbrowne.com/ser-42.html

Finally, you may be able to use the IN operator in the Criteria of your
DSum(), instead of a bunch of ORs. For example, instead of:
"[City] = 'New York' OR [City] = 'Springfield' OR [City] = 'Des Moines'
OR ...
you could use:
"[City] IN ('New York', 'Springfield', 'Des Moines')"
 
Top