Solved DSum issue when using dynamic criteria

[stardustvega]

I have data like this:

ID	Item	Qty
2	Item 1	2
3	Item 2	1
4	Item 1	1
5	Item 3	5

On a report, I want to return the quantity of the current item. That is, if the current record is Item 1, I want to get 3. The items aren't linked to IDs (e.g. Item 1 doesn't always have an ID of 53), but the names are standardized. Let's say that this data is in the table "TestTable".

I've tried using this for the Control Source of a text box:

=DSum("Qty","TestTable","Item=" & [Item])

I've also tried using this in a query that pulls from TestTable:

TestTotal: DSum("Qty","TestTable","Item=" & [Item])

Both just give a #ERROR value.

I'm scratching my head because it seems like the right syntax. Can you not use a dynamic value with DSum (e.g. sum the current item)? If this isn't the right approach, how can I go about doing this.

PS: I don't want to use the totals function for the section in my report because I need this value to be somewhere besides the section footer.

 

[theDBguy]

Have you tried?

=DSum("Qty","TestTable","Item='" & [Item] & "'")

 

[stardustvega]

Have you tried?

=DSum("Qty","TestTable","Item='" & [Item] & "'")

Oh, yes, that's it. I forgot that strings don't bring their own quotation marks in Access. Thank you!

 

[theDBguy]

Oh, yes, that's it. I forgot that strings don't bring their own quotation marks in Access. Thank you!

You're welcome. Glad we could help. Good luck with your project.