Form Field Not Calculating Properly

[RMittelman]

I have a form which displays student data. It has a bunch of fields, but one particular field is not properly calculating.

The form is bound to a table called StudentProfile.


In the table, the fields in question, which hold various fees, are called:

• Tuition
• Application
• Books
• Activity
• Registration
• OtherFee

The bound controls are named so as not to confuse anything:

• txtTuition
• txtApplication
• txtBooks
• txtActivity
• txtRegistration
• txtOtherFee

Finally, I have a text box called txtTotalFees, whose ControlSource is:
"=[Tuition]+[Application]+[Books]+[Activity]+[Registration]+[OtherFee]"

Problem is, the total fees do not reflect the Application field. No matter what I put in that text box, the total fees do not change. Changing any of the other text boxes causes the total to update appropriately. But, the total NEVER includes the Application field amount.

The data source and bindings are correct, because the underlying table record IS updated, even including the Application field. Changing most fields cause the total textbox to be updated immediately. However, even moving to next record and back, the Application field is not included in the total the way it should be.

I tried to use full name of field after reading that suggestion in a forum ([StudentProfile]![Application]), but that just gives me "#Name?" in the total box, so that doesn't work.

Now that I think of it, is it possible that the name "Application" is a reserved word? I know it is used in VBA code. If that's true, can it be escaped so the "keywordness" is ignored? Or is the only solution to rename the field in the data table?

Thanks...

[JoeM]

Now that I think of it, is it possible that the name "Application" is a reserved word? I know it is used in VBA code. If that's true, can it be escaped so the "keywordness" is ignored? Or is the only solution to rename the field in the data table?

If that field is the ONLY one giving you trouble, I would tend to believe that you hit the nail on the head with your comment above - it is probably a reserved word and that is what is causing your headaches. As big a pain as it may be, I would recommend changing the field name in the table.

[RMittelman]

Thanks, Joe. I "kinda sorta" proved that, by replacing the field name with the textbox name in the totaling formula, and it then would calculate properly. Unfortunately, you would need to put an Nz function around that field, just in case the textbox is empty. Otherwise, you just get a blank total textbox. All in all, your suggestion of changing the field name is safest. Thanks a lot.

[JoeM]

Yes, I use the NZ function a lot. If you have any numeric fields that you wish to use in calculations that may contain NULL values, you need to use the NZ function to get good results. Whenever possible, I set up my numeric fields to default to zero instead of NULL to avoid this, but sometimes you don't have that luxury or other situations lead to NULLS in queries (especially when doing OUTER JOINS!).