form footer total not working

[VBAPrincess]

I have an Access subform which has totals in the footer. The calculated field should display the total premium for the displayed account. The subform has worked just fine until we moved to 2010. However, I have two employees using Citrix to access the database. When they view the form the calculated field is blank. I recreated a new (simpler) form thinking perhaps I needed to rebuild the form but it wouldn't work there either. I do not get #Error, instead I get nothing. I've attached an image to show what I'm trying to do.

The query behind the form uses the field Premium which is in the table. It is not a calculated field. The textbox in the footer has the formula =Sum([Premium]). There are no "nulls" in the records as the field defaults to zero. Using the formula =Sum(Nz([Premium],0)) does not change the outcome. The database file is an MDB as it was created in Access 2003. We are now using Access 2010 on Windows 7.

Thanks in advance! Hopefully I'll find it's something simple I've overlooked!

Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[VBAPrincess]

Finally got the calculations to work but had to use DSUM instead of just SUM. I found another thread on another site where someone was able to get AVG to work but not SUM, but AVG didn't work for me either.

=DSUM("Premium","qfrmBuildings","CodeKey=" & [CodeKey])

As a side note, on my main form I have 2 textboxes -- one displays the premium total, and another shows the premium + another value. I kept getting a #Type! error in the second textbox which I finally resolved by putting the calculation in the Form_Current event rather than in the textbox's control source.

It's really weird that I couldn't get this to work the way it's worked before. I could understand it not working if the field I was trying to sum was a calculated field, but it wasn't. The field came directly from the table. Also, there was no #Error message displayed.

Hope this helps someone else!

Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[dhookom]

Any chance you could provide the SQL view of the report's record source?

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

Sorry, I meant the subform's record source.

Duane
Hook'D on Access
MS Access MVP

 

[VBAPrincess]

Sorry Duane! I've been on vacation and didn't get this uploaded before I left. Here's the SQL behind the form:

SELECT Buildings.CodeKey, IIf(IsNull([LocationNumber]),99999,[locationNumber]) AS Expr1, Locations.LocationNumber, Buildings.LocationID, Buildings.BuildingNumber, Buildings.BuildingID, Buildings.ConstructionClass, Buildings.ProtectionClass, Buildings.OperationClass, Buildings.Coverage, Buildings.Value, Buildings.Deductible, Buildings.DeductibleFactor, Buildings.Rate, [DeductibleFactor]*[Rate] AS NetRate, Buildings.Premium, Buildings.BuildingArea, Buildings.BuildingRoofConstruction, Buildings.BuildingYearConstructed, Buildings.BuildingConstructionType, Buildings.Comments, Buildings.Sprinkler, Buildings.DeductibleHours, Buildings.StreetAddress, Buildings.City, Buildings.State, Buildings.Zip, Buildings.Latitude, Buildings.Longitude, Buildings.CoverageDesc
FROM Buildings LEFT JOIN Locations ON Buildings.LocationID = Locations.LocationID
ORDER BY Buildings.CodeKey, IIf(IsNull([LocationNumber]),99999,[locationNumber]), Locations.LocationNumber, Buildings.BuildingNumber, Buildings.Coverage;

Now I'm struggling with getting the totals to update when you edit a record. I've tried code in the AfterUpdate event of the subform and that isn't working. If I step thru the code in break mode, it works just fine, but it doesn't execute properly when I let it run. This is so frustrating!

Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[dhookom]

What is the data type of Premium? When you view it in datasheet view is it right aligned?

Duane
Hook'D on Access
MS Access MVP

 

[VBAPrincess]

Sorry it's taken a while to respond Duane. (I finally just wrote code to make the subform total the values in the textboxes each time something changes.) To answer your question, the field is a currency data type.

To add more to this problem ... I have actually had this same thing happen in another database. I've uploaded two more images to show the same client with totals and one without. There are three breakdown totals showing premium by coverage and then a final grand total. Again, in this database premium is a currency field. The user in this dept who cannot see the totals is working in the office, not remotely like those users mentioned in my original post.

The database is a 2002-2003 format mdb file. Could that be the issue? If so, why wouldn't it happen to everyone?

All of the users in this department, including myself, are using Windows 7 & Office 2010. We are in a corporate environment where we have all been migrated in the past 4-6 months to Office 2010. We upgraded to Win 7 in the past year. This is just so bizarre!

Diana
VBA Princess
-- I'm hoping to grow up to be a Goddess!

 

[dhookom]

I agree it's bizarre. It would be interesting to have a calculated column in the record source of:
dblPremium: cDbl([Premium])

Then attempt to replace =Sum([Premium]) with =Sum([dblPremium])


Duane
Hook'D on Access
MS Access MVP