Datediff trouble

[AccessIdiot]

Wow I'm failing miserably today. :-(

I have a table with three colums: fill_start, fill_end, fill_dur. Fill_dur is supposed to calculate the difference in minutes between fill_start and fill_end. I know the calculation is simply =DateDiff("n", [Fill_Start], [Fill_End]) but I have no idea WHERE to put this to make it work.

Some background that might make a difference: Fill_Start and _End are date/time short time format (17:34 for example). Fill_Dur is a number field.

If I put the DateDiff function in an unbound textbox (called txt_DateDiff) then it calculates the value beautifully. I tried setting Me.Fill_Dur = Me.txt_DateDiff in every event conceivable (afterupdate, getfocus, lostfocus, etc etc) to no avail. I also put the function into the form on current event etc.

Where am I supposed to put this beast?

thanks for any help
melissa

[Rabbit]

Wow I'm failing miserably today. :-(

I have a table with three colums: fill_start, fill_end, fill_dur. Fill_dur is supposed to calculate the difference in minutes between fill_start and fill_end. I know the calculation is simply =DateDiff("n", [Fill_Start], [Fill_End]) but I have no idea WHERE to put this to make it work.

Some background that might make a difference: Fill_Start and _End are date/time short time format (17:34 for example). Fill_Dur is a number field.

If I put the DateDiff function in an unbound textbox (called txt_DateDiff) then it calculates the value beautifully. I tried setting Me.Fill_Dur = Me.txt_DateDiff in every event conceivable (afterupdate, getfocus, lostfocus, etc etc) to no avail. I also put the function into the form on current event etc.

Where am I supposed to put this beast?

thanks for any help
melissa

Well, as a rule of thumb, you don't store calculated values like that in a table because of the overhead required for upkeep.

You would only calculate it when you need it.

[AccessIdiot]

What's the overhead? Is it really so much that it's not worth doing? Is it the math involved that slows things up?

thanks for the help

[Rabbit]

What's the overhead? Is it really so much that it's not worth doing? Is it the math involved that slows things up?

thanks for the help

Well, as you can see you need to recalculate whenever any of the related fields are changed. It's also a waste of space as all the information is already avaiable to calculate the value if you need it.

But if you want to store it anyways, have a bound textbox to Fill_Dur. And in the After Update events of each textbox that affects the calculation, recalculate the Fill_Dur field. There's no need for a third box. You'll also want to lock the Fill_Dur field.

[AccessIdiot]

Thanks for that. I discussed with the guys who wanted it in the first place and told them it would be a drag on the db so they relented and said okay to having it calculate at report time.

Thanks for helping me!

[Rabbit]

Not a problem.

If they want to see that number just have it in an unbound textbox with the control source set to the calculation.

And at report time it can recalculate it. So it is basically what they want without actually storing the value.

[AccessIdiot]

Spot on, as usual! :-)

[Rabbit]

Spot on, as usual! :-)

Good Luck!

[NeoPa]

Hey Rabbit - 4 digit posts - cool :)
Melissa, Rabbit's already told you exactly what I would have said. It's definitely the way to handle it. Storing calculated (or calculable) fields is just a way of asking the gods to give you a head-ache ;) Easier to go straight there with a mega drinking binge. Probably more fun too :D

[AccessIdiot]

Yeah I don't know what we were thinking. There are a bunch of other things we were planning on leaving out simply because they didn't need to be stored but could just be inserted at report time so I don't know why we didn't throw this one in that pile.

No binge drinking for me until my baby girl is done with HER drinking if you know what I mean! :-D

[NeoPa]

I surely do :)
I can't say I've been there exactly (not in this lifetime at least) as I'm definitely of the wrong gender for that sort of thing, but I lived through it from the other side a couple of times all the same.