DSUM Criteria in a Report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I would be grateful for any assistance with the following:

I am creating a report and was thinking of using DSUm from a table.

DSum («expr», «domain», «criteria»)

I know that "expr" is the column to sum and table name is the "domain",
however is it possible to set the "criteria" to be a different field to
"expr" within the same Domain.

eg. DSum ("Mar-05","Matrix","Type='ISDN30'")

The idea being to look up the specific value of one field within a table.

Please accept my thanks in anticipation.

Kind regards

Colin
 
Yes. Add an equal sign to the front of that expression, and it should work
if the Matrix table has a Number field named Mar-05 and Text field named
Type, and some of the records have the value ISDN30 in the Type field.

Your expression produces the same result as this query would:
SELECT Sum(Matrix.[Mar-05])
FROM Matrix
WHERE Matrix.Type = 'ISDN30';
 
I am creating a report and was thinking of using DSUm from a table.
DSum («expr», «domain», «criteria»)

I know that "expr" is the column to sum and table name is the "domain",
however is it possible to set the "criteria" to be a different field to
"expr" within the same Domain.

eg. DSum ("Mar-05","Matrix","Type='ISDN30'")

The idea being to look up the specific value of one field within a table.
Click to expand...

DSum ("Mar-05","Matrix","Type='" & [FieldName] &"'")

Where FieldName is the name of the field that contains the value you're
looking for.

Tom Lake
 
Many Thanks

Allen Browne said:
Yes. Add an equal sign to the front of that expression, and it should work
if the Matrix table has a Number field named Mar-05 and Text field named
Type, and some of the records have the value ISDN30 in the Type field.

Your expression produces the same result as this query would:
SELECT Sum(Matrix.[Mar-05])
FROM Matrix
WHERE Matrix.Type = 'ISDN30';

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

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

colin said:
I would be grateful for any assistance with the following:

I am creating a report and was thinking of using DSUm from a table.

DSum («expr», «domain», «criteria»)

I know that "expr" is the column to sum and table name is the "domain",
however is it possible to set the "criteria" to be a different field to
"expr" within the same Domain.

eg. DSum ("Mar-05","Matrix","Type='ISDN30'")

The idea being to look up the specific value of one field within a table.

Please accept my thanks in anticipation.

Kind regards

Colin
Click to expand...
Click to expand...
 
DSum («expr», «domain», «criteria»)

What If you need to use additional criteria? Using colins eg.

eg. DSum ("Mar-05","Matrix","Type='ISDN30'")

If say I needed only records with ISDN30 AND a specific formtype? Would it
be something like this?

DSum ("Mar-05","Matrix","Type='ISDN30'" AND "Formtype='labels'")

Neither AND nor & seem to work for me or maybe I have some quotes out of
place. Any code suggestions for instances like this?



Allen Browne said:
Yes. Add an equal sign to the front of that expression, and it should work
if the Matrix table has a Number field named Mar-05 and Text field named
Type, and some of the records have the value ISDN30 in the Type field.

Your expression produces the same result as this query would:
SELECT Sum(Matrix.[Mar-05])
FROM Matrix
WHERE Matrix.Type = 'ISDN30';

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

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

colin said:
I would be grateful for any assistance with the following:

I am creating a report and was thinking of using DSUm from a table.


I know that "expr" is the column to sum and table name is the "domain",
however is it possible to set the "criteria" to be a different field to
"expr" within the same Domain.

eg. DSum ("Mar-05","Matrix","Type='ISDN30'")
Click to expand...
Click to expand...
 
Back
Top