Multiple Criteria with DSUM in a Report

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

Guest

Greetings -

I'd be grateful for any help on the correct syntax using an AND operator in the criteria within a DSUM function. In my particular case, I'm trying to sum the values for MONTH where the Profit Center [PC] equals the PC in the current report subgroup and the account group [Grp2] equals a fixed string. One of several failed attempts looks like this

=DSUM("[MONTH]","RecordsourceQueryName","[PC]="&[PC] AND [Grp2]='In-Store Sales'

The ' "[PC]="&[PC] ' works fine and successfully returns the Procift center code in a DLOOKUP verstion of the function, but I can't seem to get around some conflict with the combination of the ' "[PC]="&[PC] ' and the string criteria with the AND operator

Any suggestions
Does anyone know the technical term for the dynamic or self-referencing critiera like ' "[PC]="&[PC] '

Many thanks
 
If this text box is in the [PC] footer and RecordsourceQueryName is the
report's record source then try:
=Sum(Abs([Grp2]="In-Store Sales") * [Month])
This is a much more efficient expression than DSum().
If you have to use DSum() then try:
=DSUM("[MONTH]" , "RecordsourceQueryName" , "[PC]="& [PC] & " AND
[Grp2]='In-Store Sales'")
I would not use the DSUM() since if you open the report with a where clause,
the values may not be correct.

--
Duane Hookom
MS Access MVP
--

Galen Moore said:
Greetings -

I'd be grateful for any help on the correct syntax using an AND operator
Click to expand...
in the criteria within a DSUM function. In my particular case, I'm trying
to sum the values for MONTH where the Profit Center [PC] equals the PC in
the current report subgroup and the account group [Grp2] equals a fixed
string. One of several failed attempts looks like this:
=DSUM("[MONTH]","RecordsourceQueryName","[PC]="&[PC] AND [Grp2]='In-Store Sales')

The ' "[PC]="&[PC] ' works fine and successfully returns the Procift
Click to expand...
center code in a DLOOKUP verstion of the function, but I can't seem to get
around some conflict with the combination of the ' "[PC]="&[PC] ' and the
string criteria with the AND operator.
Any suggestions?
Does anyone know the technical term for the dynamic or self-referencing
Click to expand...
critiera like ' "[PC]="&[PC] ' ?
 
Definitely use the Sum() rather than the DSum() since it is much more
efficient and accurate.

--
Duane Hookom
MS Access MVP
--

Galen Moore said:
Thank you, Duane, both worked perfectly. Since I am indeed puuting this
Click to expand...
in the PC Footer, it follows that I shouldn't need to complicate my criteria
syntax with a redundant element like [PC] in this case.
 
Back
Top