DSum syntax issue

[bhoran]

I am trying to use DSum in a calculated control in a report to work out a variance.

The report is running off a standard select query over 2 crosstab queries and is as follows.

=DSum("1","BHtest1CrossQueryRep","LD"='AOP')

I am trying to get the Sum of field [1] in query [BHtest1CrossQueryRep] where [LD] = AOP

When I run it like this it works fine.
=DSum("1","BHtest1CrossQueryRep&quot

so it seems to be my criteria selection that is screwed up.

I eventually want it to be something like this
=(DSum("1","BHtest1CrossQueryRep","LD"='AOP')-(DSum("1","BHtest1CrossQueryRep","LD"='ACT'))/(DSum("1","BHtest1CrossQueryRep","LD"='ACT'))

to give me a % difference.

If anyone can help or suggest a better solution I would be greatful.

Cheers

 

[bhoran]

Oops sorry I was wrong although I get a number from this argument =DSum("1","BHtest1CrossQueryRep&quot it is incorrect.

 

[dhookom]

'A push into the proper direction. I assume the report is based on BHtest1CrossQueryRep. To sum the values in a field named [1] where a field name [LD]="AOP" you can use this expression:
=Sum( Abs([LD]="AOP&quot * [1]) )
This is much more efficient than
=DSum("[1]","BHtest1CrossQueryRep","[LD]='AOP'&quot
Also note the differences between my DSum() syntax and your attempt.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.