By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,720 Members | 2,135 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,720 IT Pros & Developers. It's quick & easy.

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

P: 256
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:

Expand|Select|Wrap|Line Numbers
  1. =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. :-)
Dec 4 '18 #1
Share this Question
Share on Google+
5 Replies

Expert Mod 15k+
P: 31,489
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.
Dec 5 '18 #2

Expert Mod 10K+
P: 12,365
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.
Dec 5 '18 #3

Expert Mod 2.5K+
P: 3,283
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.
Dec 5 '18 #4

P: 256
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
Dec 6 '18 #5

Expert Mod 15k+
P: 31,489
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 ;-)
Dec 7 '18 #6

Post your reply

Sign in to post your reply or Sign up for a free account.