stardustvega
Member
- Local time
- Today, 06:19
- Joined
- Feb 4, 2022
- Messages
- 36
I have data like this:
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:
I've also tried using this in a query that pulls from TestTable:
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.
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:
Code:
=DSum("Qty","TestTable","Item=" & [Item])
I've also tried using this in a query that pulls from TestTable:
Code:
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.