Solved footer totals in subform (1 Viewer)

[ClaraBarton]

The attached database has a form called frmsubCategory. It worked fine until I got fancy and put in Richard Ross' fields that calculate in the field. The pink fields should not be visible but are attached to Debit and Credit fields and totaled at the bottom.
The totals in the footer return #Error.
If I remove the fields called txtDebit and txtCredit (which should have nothing to do with the TDebit and TCredit fields, but lay on top of them) the totals work. If I leave them in they don't. What am I missing here?

 

[June7]

Why do you have txtDebit and txtCredit fields? These are text, not number type. What purpose do they serve?

Both Sum() calcs reference Debit field.

What do you mean by "remove"? I unbound the textboxes and removed fields from query and totals still don't work.
I couldn't get your form to work so I built a new one. Works until I add textboxes bound to txtDebit and txtCredit. I can't explain this.

Your Nz() calculated values are not used by the Sum() expression. Aggregate functions must pull from fields not textboxes. Why not display them and eliminate the text fields?

 

[ClaraBarton]

If you delete the txtDebit and txtCredit the totals work. They are text boxes for calculations. They work with an eval function. The Debit and Credit fields are not text. The sum does pull from the field and not textboxes.

 

[June7]

Why would you even need to use text fields with Eval() function to do calcs? I have never seen this.
My point is, the Sum() calcs work with the Nz() textboxes visible and the text fields eliminated. I simply bound form to table instead of query.

Made another attempt to replicate your original form. I tried copying your controls from the original and get same issue. So i created new controls for the Nz() calcs and now everything works, including the text fields, in the new form. So I deleted the Nz() textboxes and created new ones in your original form. Calcs work.

Sometimes weirdness is corruption that cannot be explained.

 

[ClaraBarton]

Go to one of the white fields, txtDebit and type 1 + 5... Enter. It's for doing calculations to set the debit amount. It works very well.
I know the footer sums work when the txtdebit and txtcredit are eliminated. That's my point. Why should they affect it in any way?

 

[June7]

Did you fully read my previous post? I was able to fix your form by deleting and recreating the Nz() textboxes.

I still don't understand need for the text fields. Why not just enter a value into number field?

Why use Nz() at all? 0 does not show because of Format setting.

 

[ClaraBarton]

Deleted nz fields and replaced with debit and credit. Still do not work unless I delete txtDebit and txtCredit.

 

[ClaraBarton]

I still don't understand need for the text fields. Why not just enter a value into number field

Have you ever done data entry? Doing math in a field is typical of accounting software. When you need to quickly split a sum into separate accounts and add sales tax at the same time, a math field can be a real time saver. I found this tip in a video on Richard Ross' site and tried to implement it. I should go to him with my question. Thank you for your time.

 

[Gasman]

I still don't understand need for the text fields. Why not just enter a value into number field

Have you ever done data entry? Doing math in a field is typical of accounting software. When you need to quickly split a sum into separate accounts and add sales tax at the same time, a math field can be a real time saver. I found this tip in a video on Richard Ross' site and tried to implement it. I should go to him with my question. Thank you for your time.

Always a goof idea to ask the person you got the code from in the first place?
They would be the better person to identify the issue.

FWIW I always used the numeric keypad on the keypad.
I believe you can get it to work with unbound controls, but then the value is repeated for every record because of that.

Could this have something to do with it?

I was trying to see if setting Null fields to 0 would stop it happening?

 

[Gasman]

Half of the DB is missing, so I had to comment out your code for the update.
BTW do not select any records when you are adding new, start with an empty recordset.

TBH not sure how I fixed it, as I had to download a few times, as I change so much and got it working, but could not see which made it happen.
I deleted your txtControls and added them again via Add Existing fields.

Your credit field was still referencing debit in totals as well?

 

[June7]

I have done lots of data entry, even into accounting programs (QuickBooks, Great Plains) and never seen a "math" textbox.

Could you provide link to the Richard Rost tutorial? Only one I can find that seems relevant shows creating calculated field in table, not a "math" textbox for user to enter an expression.

Disappointed that Richard shows naming convention using spaces and punctuation/special characters.

I tried to replicate my success with a new download of your db. I had to delete and recreate both the Nz() and the text textboxes.

Again, I don't see that Nz() function is needed. And if these textboxes will not be visible, why bother with Format?

 

[Gasman]

I believe the o/p is trying to emulate Quicken.
One can do math in their controls, but it is the same control that holds the amount. Access cannot do that without a lot of work I believe?

 

[ClaraBarton]

access math in field - Google Search

 

[ClaraBarton]

My controls look like one control because one goes on top of the other. The pink fields are not visible.

 

[Gasman]

Fwiw I only just tried the math in Quicken on a hunch.
I have always entered a value, and my Quicken is 2004.

 

[ClaraBarton]

ya nailed it. It also works in QuickBooks, MoneyDance, Money... Most any credible accounting package.

 

[Gasman]

ya nailed it. It also works in QuickBooks, MoneyDance, Money... Most any credible accounting package.

Money?, now that is going back?

 

[xavier.batlle]

Disappointed that Richard shows naming convention using spaces and punctuation/special characters.

No, Richard never uses spaces or puctuation/special characters at all.

 

[ClaraBarton]

and neither did I

 

[June7]

No, Richard never uses spaces or puctuation/special characters at all.

Sorry, turns out it was not a Richard Rost page I landed on.