DSUM Criteria in a Report

[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

 

[Allen Browne]

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';

 

[Tom Lake]

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.

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

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

Tom Lake

 

[Guest]

Many Thanks

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.

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

 

[Guest]

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?

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.

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'")