DSUM - Please help!

[acro88]

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.

 

[fneily]

Try to use underscores between words. It helps in coding and reading. Also, must your names for queries and tables be so long? Just curious.

It's easier to show a running sum on a form or report. See:
ACC2000: How to Use DSum to Create a Running Sum on a Form

http://support.microsoft.com/?kbid=210495

I created a table with:
TenantID
Date
Amount_Paid
Payment_Method
Notes
Actual_Rent_Due

Then I created a query to pick off a certain tenant. I included the TenantID: NOTE: IT MUST BE SORTED BY DATE!

SELECT tblTenant.TenantID, tblTenant.Fname, tblTenant.Lname, tblTenant.Date, tblTenant.Amount_Paid, tblTenant.Payment_Method, tblTenant.Notes, tblTenant.Actual_Rent_Due, 12*(DatePart('yyyy',[Date])-2008)+DatePart('m',[Date]) AS MonthNumber, DatePart('d',[Date]) AS DayNumber
FROM tblTenant
WHERE (((tblTenant.Fname)="somename"))
ORDER BY tblTenant.Date;

I then used the query as the recordsource for a form. On the form, I created a textbox, called it Total. The control source of the texbox is:
=DSum("[Amount_Paid]","[Rent_Account_For_Specific_Tenant]","[Date] <= Forms![Rent_Account_for_Specific_tenant_form]![Date]")

So as you advance through the records, the Amount_Paid's running sum is kept by date.

If you do a search on this forum, and other Access forums, you will find there is a way to build a function to use in a query to make a running sum. But a form and report are more straight forward.

 

[acro88]

Thank you for your post. I acknowledge your point about underscores and will change that now.

I know that normally a running sum would be used on the report. The problem is that I need to process the information in another query, based on the contents of the running total for a given date. I've tried to follow advice on how to make the running sum, but I keep getting this error message about the tenant number, which doesn't even occur on this query.

 

[fneily]

See:
faq701-5268