Linking calculated fields to a control source

[chasmith17]

Hello,
I have 6 time fields that sum to a total time field in a form and I want to tie that into a Control Source called "Time Spent Hour". Can someone tell me what experession I need to type in the Control source box? Right now it looks like this: =([timehr 1]+[timehr2]+[timehr3]+[timehr4]+[timehr5]+[timehr6])
Thanks,

 

[PHV]

Any chance you could say us what's wrong with your posted ControlSource and why ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[chasmith17]

I want the total time field to show up in the record. As it is right now there is no link to the table. I need to include the name "Time Spent Hour" so it is recorded in the table. Thanks for your help PHV

 

[PHV]

It's rarely a good idea to store derived/calculated values in a DB.
Have a look here:

http://r937.com/relational.html

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[chasmith17]

PHV,
I have read this article before and have a pretty good idea of the normalization process, however straight from the artical "Occasionally, you may need to denormalize for performance". Here is my Occasional denormalization. Are you saying that this can't be done?

 

[ChopinFan]

Chasmith,

Two thoughts.

First, how will that total be used? If the total itself would only be relevant on a report or such, it is not necessary to store the information in the table.

Second, if you do decide to go that route anyway, I would set up a hidden field in the form that is bound to the table, then have the afterupdate() event of each of the 6 time fields set the value of that hidden bound field to the sum of all 6. Make sense?

Just a suggestion...

 

[PHV]

Are you saying that this can't be done?|/i]
No, I just warned ;-)
Set the ControlSource of your [total time] control to the denormalized field.
Then populate it with the formula ([timehr 1]+[timehr2]+...) in the Current and BeforeUpdate event procedures of the form and in the AfterUpdate event procedure of each timehr control.
Tip: Have a look at the Nz function.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[chasmith17]

Thanks for your help guys,
I thought about what I was trying to do and it turns out I shouldn't ever need to run any queries or reports for this form. All I need is right there in the form. Thanks agian for helping this newby.