Need help with DSUM

[alex88]

I am currently trying to implement a new database system for work, but I've come to a brick wall. If anyone can help I will be extremely grateful because I've been pulling my hair out for hours. Here's the problem:

I have a query in which there is a field called amount paid. In a new query I want a field with a running total of the amount paid to date. I've tried many versions of the same code, but keep getting the same error message:

The expression you entered as a query parameter produced this error: 'The object doesn't contain the Automation object 'Tenant number?.''

Here's the code:

SELECT [Rent account for specific tenant].Date, 12*(DatePart('yyyy',[Date])-2008)+DatePart('m',[Date]) AS Monthnumber, DatePart('d',[Date]) AS Daynumber, [Rent account for specific tenant].[Amount paid], [Rent account for specific tenant].[Payment method], [Rent account for specific tenant].Notes, [Rent account for specific tenant].[Actual rent due], DSum([Amount paid],"Rent account for specific tenant","(12*(DatePart('yyyy',[Date])-2008)+DatePart('m',[Date]))<" & [Monthnumber] & "" & "OR ((12*(DatePart('yyyy',[Date])-2008)))+DatePart('m',[Date])=" & [Monthnumber] & "" & "AND DatePart('d',[Date])<=" & [Daynumber] & "") AS [Total paid]
FROM [Rent account for specific tenant]
GROUP BY [Rent account for specific tenant].Date, 12*(DatePart('yyyy',[Date])-2008)+DatePart('m',[Date]), DatePart('d',[Date]), [Rent account for specific tenant].[Amount paid], [Rent account for specific tenant].[Payment method], [Rent account for specific tenant].Notes, [Rent account for specific tenant].[Actual rent due];

If anyone has any ideas or alternate methods please let me know.
Thank you very much, Alex.

 

[Pat Hartman]

Calculating a running total is EXTREMELY inefficient and is ONLY possible if each record has a uniqueID that will provide the correct sort order. If you search, there are examples to be found here. Choose one that utilizes a join rather than DSum() to be as efficient as possible.

Calculate the running sum in a report where it is extremely efficient. Set the RunningSum property of the control to OverAll or OverGroup depending on whether or not you want the sum to break.

 

[bs0d]

I know this is old, but reading Pat's response made me second guess using DSum. I did some searching and as he suggested, there is a way to avoid using it. You can execute a running totals query and avoid the aggregate function like this (used in MS Access):

SELECT 
Sum(a2.Sales) AS SumOfSales
FROM tblDailyReadings AS a1, tblDailyReadings AS a2
WHERE (((a2.ReadingDate)<=[a1].[ReadingDate]) AND ((a1.Item_KEY)=[a2].[Item_KEY]))
GROUP BY a1.ReadingDate
ORDER BY a1.ReadingDate;

Cheers