"Text is too long to be edited" error in a calculated control. Work around??

[DanicaDear]

I have a timesheet (Access form) with about 20 fields where employees enter their work hours. (Example: Vacation, Repair Hours, Jury Duty, Sick Leave....) In a calculated unbound control at the bottom, I want to sum up the hours to make sure the employee has 10 hours. I have to take care of null fields, so this is what I'm using in my calculated control:

=Nz([ShopOrderHours],0)+Nz([VacationHours],0)+Nz([SickLeaveHours],0)+Nz([FamilySickLeaveHours],0)+Nz([FMLAEmpHours],0)+Nz([FMLAFamHours],0)+Nz([SafetyMeetingHours],0)+Nz([SafetyTrainingHours],0)+Nz([JuryDutyHours],0)+Nz([MeetingHours],0)+Nz([TrainingHours]

This works great...except you see that's only 11 of my fields. When I enter them all, it says "Text is too long to be edited," and I can't save it. So, I can't sum ALL the fields. I don't know how to write VBA but I am familiar with how it works, after update, etc. If that's the direction I'll need to go, I'll need some hints. :-)

What solutions do you smarter people think may work to accomplish my goal?

If NeoPa or ADezii see this----HEY Y'ALL! Just a friendly HEY! A "Shout Out" as we call it here in the States. :-)

[NeoPa]

Hey Danica.

I notice you posted. Very best wishes and call some time if you'd like some help. Just arrange in advance if you can ;-) I'd love to hear from you though.

As you're calling a function anyway (Nz) you may as well write your own that expects x number of parameters and adds them up.

Shout if you need help with this.

[Rabbit]

I don't know if a redesign is viable but considering the number of columns and considering how many nulls you must have in that data, it might work better if you redesigned the table to be taller than wide.

[twinnyfo]

A function (as NeoPa suggests) is a good and viable idea, but another way to approach this is to use either the OnCurrent or AfterUpdate events to tally the fields. But now that I think of it, that is essentially the same as a User-Defined Function anyway.

Never mind. Just thinking out loud again.

[DanicaDear]

Ok guys...I'm about to display my inner genius!! I avoid code when possible because I'm not independent with it. But I still came up with a working solution. Since the one control would not hold code for all 20 of my fields, I split it up into two controls. I added up 10 in one control, and 10 in the other. I made those "not visible." Then, for my visible control, I added up the two new middleman controls. BOOM!!! SOLVED! LOL!

NeoPa's gonna kill me. haha

[NeoPa]

DanicaDear:
NeoPa's gonna kill me. haha

Actually that's a good idea. It may not be the best way. That probably involves looking at the overall problem from a different angle, as this does come across as a little clumsy. Nevertheless, with that problem, you've come up with a clever and appropriate solution.

PS. I'd only ever kill you in fun ;-)