Weird sum #error in form footer

[Vidar13]

I have no idea why this is happening. I created a new form and brought in the same bound controls (form based on query) and used the sum() to total records across form's recordset and it works fine.

For some reason, it has gone buggy in a form that I've already developed.

It is NOT a calculated control.. it is bound by query, directly to a table field.

Is this a known bug in Access 2000 where a form can suddenly refuse to calculate fields?

 

[Dustman]

Well.. it is a Microsoft product so who knows. I have had several instances where things just goofed for no reason. I usually have to rebuild part of the form. You might try doing the calculations in the control instead of in the query, see if that wakes it back up. -Dustin
Rom 8:28

 

[Vidar13]

I should correct myself and clarify that what I meant was that this sum control field was not BASED on a calculated control. I am able to use a query to make the total, but I want the total to appear on the form in the form of a control.

I noticed another article from a while back that had the same problem, but the answer was to try manually re-adding the source control field and then re-create the sum control field. I tried that and it had no effect.

 

[Dustman]

Can you show me the SQL code that works in the query? -Dustin
Rom 8:28

 

[Vidar13]

SELECT [Daily Entry].Batch, Sum([Daily Entry].Deposit1) AS SumOfDeposit1, Sum([Daily Entry].Deposit2) AS SumOfDeposit2, Sum([Daily Entry].Deposit3) AS SumOfDeposit3, Sum([Daily Entry].Deposit4) AS SumOfDeposit4, Sum([Daily Entry].VisaMC) AS SumOfVisaMC, Sum(nz([Deposit1])+nz([Deposit2])+nz([Deposit3])+nz([Deposit4])+nz([VisaMC])) AS [Total Red Figure]
FROM [Daily Entry]
GROUP BY [Daily Entry].Batch
HAVING ((([Daily Entry].Batch)=[forms]![Daily Reconciliation]![Batch]));

It works fine, but trying to put a sum control on the same form this is pulling the "batch number" from results in #error results each time, using:


=sum(([Deposit1])+([Deposit2])+([Deposit3])+([Deposit4])+([VisaMC]))