* Windows 7 Pro
* Access 2010 v14.0.7182, with SP1
* I have used Access before but that was many years ago. I'm basically new to A2010.
* I'm using a MDB file.
I have a report. The query behind the report looks at a table and simply filters in certain part numbers, the field is called [item_no]. In the report I want to calculate the qty sold by part number, so the table in the query behind the report has many records with the same part number, each record will be a sales transaction.
Report: I have a group footer for the part number. In this group I want to sum the qty sold for only this part number. So in the group I have these text boxes:
* txtGrpFtrItemNo: Just contains part number to be used in DSum().
* txtGrpFtrItemDesc: Part description. Not important here.
* txtGrpFtrSumQty1: where I want to sum the quantities sold for only this part number.
In this group footer, besides the part number and part desc boxes, there will be 4 text boxes with DSums. For fiscal year 1, there will be a sum of sales dollars and sales qty, and for fiscal year 2, there will be the same 2 fields, sum of sales dollars and sales qty.
My DSum in txtGrpFtrSumQty1 is: =DSum("[qty_ordered]","tblSalesLastYear","[item_no] = '[txtGrpFtrItemNo]' and [billed_dt]>= '20150601 and [billed_dt] <= '20160531'")
I've also tried this DSum: =DSum("[qty_ordered]","tblSalesLastYear","[item_no] = "+[txtGrpFtrItemNo]+" and [billed_dt]>= '20150601 and [billed_dt] <= '20160531'")
Table fields:
* [billed_dt] represents a date but is actually a string in the format YYYYMMDD.
* [item_no] is a string field.
Whenever I run the report in Report View the text box with the DSum gives me "#Error". There is no other info available.
So how do I use a DSUM in a group footer like this? I will have to put other DSUMS in this group footer too.
I've already searched the Access help and in 20 years of using Access off and on I've found it consistently useless. I've also Googled for some answers but have not found any yet.
I am too new to post a link to an image.
How can I get the DSums to work?
Thank you.
* Access 2010 v14.0.7182, with SP1
* I have used Access before but that was many years ago. I'm basically new to A2010.
* I'm using a MDB file.
I have a report. The query behind the report looks at a table and simply filters in certain part numbers, the field is called [item_no]. In the report I want to calculate the qty sold by part number, so the table in the query behind the report has many records with the same part number, each record will be a sales transaction.
Report: I have a group footer for the part number. In this group I want to sum the qty sold for only this part number. So in the group I have these text boxes:
* txtGrpFtrItemNo: Just contains part number to be used in DSum().
* txtGrpFtrItemDesc: Part description. Not important here.
* txtGrpFtrSumQty1: where I want to sum the quantities sold for only this part number.
In this group footer, besides the part number and part desc boxes, there will be 4 text boxes with DSums. For fiscal year 1, there will be a sum of sales dollars and sales qty, and for fiscal year 2, there will be the same 2 fields, sum of sales dollars and sales qty.
My DSum in txtGrpFtrSumQty1 is: =DSum("[qty_ordered]","tblSalesLastYear","[item_no] = '[txtGrpFtrItemNo]' and [billed_dt]>= '20150601 and [billed_dt] <= '20160531'")
I've also tried this DSum: =DSum("[qty_ordered]","tblSalesLastYear","[item_no] = "+[txtGrpFtrItemNo]+" and [billed_dt]>= '20150601 and [billed_dt] <= '20160531'")
Table fields:
* [billed_dt] represents a date but is actually a string in the format YYYYMMDD.
* [item_no] is a string field.
Whenever I run the report in Report View the text box with the DSum gives me "#Error". There is no other info available.
So how do I use a DSUM in a group footer like this? I will have to put other DSUMS in this group footer too.
I've already searched the Access help and in 20 years of using Access off and on I've found it consistently useless. I've also Googled for some answers but have not found any yet.
I am too new to post a link to an image.
How can I get the DSums to work?
Thank you.
Last edited:
