DSum in report

[hockeyfan21]

Cannot figure out why this won't work. Tried looking at several DSum examples but either get an error or no data at all.

I have a report grouped on "Initiative Nm". The "Initiative Nm" text box is under the Initiative Nm header. I placed a text box in that same header area with a DSum calculation as follows:

=DSum("[On Order Qty]","qryDC_Demand","[Initiative Nm]= [Initiative Nm]")

Instead of giving me the total of the inidividual Initiative Nm in the header, it is giving me the total of all combined.

Initiative Nm1 - 7,047,452 (s/b 45,678)
Initiative Nm2 - 7,047,452 (s/b 25,098)

Any ideas on what I might be doing wrong? The report is based on a separate query. Thank you!!

 

[pbaldy]

Concatenate the value like the form example and see if that works:

http://www.mvps.org/access/general/gen0018.htm

 

[hockeyfan21]

I left out what is probably an important part of my problem. The total I am trying to place in the DSum text box is for the entire set of data whereas my report is just showing a subset of that data. So, if I were to total by initiative all the "On Order Qty" for what is showing in the report for Initiative Nm1, it may come up to 50k , but overall, the On Order Qty for this Initiative Nm1 may be 250k, it just is not showing on the report as it did not meet the critieria. I need the DSum to show 250k - I hope that makes sense, kind of confusing.

thanks for the help - I will play around with it some more.

Toni

 

[hockeyfan21]

Got it!

=DLookUp("[On Order Qty]","qryDC_Demand","[Initiative Nm]='" & [Initiative Nm] & "'")

Thanks everyone for getting me on the right track - needed single quotes!

You guys rock as always!

 

[pbaldy]

Happy to help!

By the way, don't you want DSum?

 

[hockeyfan21]

I thought about it more and, since I was already summing and grouping by initiative in my query, I thought I should just use DLookup instead of DSum - does that sound correct?

 

[pbaldy]

Sure, I just noticed you were using DSum before.

 

[hockeyfan21]

Me again, I have to add another criteria to the previous DLookup, thought it would just be a matter of adding ...And ...

=DLookUp("[On Order Qty]","Demand","[Initiative Nm]='" & [Initiative Nm] & "'" And "[Distrib Mthd]='" & [Distrib Mthd] & "'")

Both Initiative Nm and Distrib Mthd are grouped headers in my report.

Using the expression above, it is pulling the first instance of Distrib Mthd in my query "Demand" and ignoring Initiative Nm, populating the same number on all records (7197)

My query "Demand" is a union query with just fields Initiative Nm, Distrib Mthd and On Order Qty.

Could someone please take a look at my syntax and let me know if you see any issues? Do I need to refer to my controls with Reports!LaunchReport!ControlNm?

Thanks!

 

[pbaldy]

You've made a common error. Delete what's in red:

=DLookUp("[On Order Qty]","Demand","[Initiative Nm]='" & [Initiative Nm] & "'" And "[Distrib Mthd]='" & [Distrib Mthd] & "'")

 

[hockeyfan21]

Thank you! So, from what it looks like, I was closing out the criteria before I added my 2nd criteria?

 

[pbaldy]

Exactly; the double quotes after the apostrophe closed off that string, leaving And out in the middle of nowhere.