Storing Data

[Calvin]

If I want to perform a calculation I am using a control source of a field.
the problem is I want to store that date in the table and it won't let me.
How do I store the calculated field in the table. My formula works here it is.
=DateAdd("m",[ServiceInterval],[tblLog Subform].[Form]![ServiceDate])
I want to store this date in the NextServiceDate field in my service table.

 

[Jeff Boyce]

Calvin

A scan of the tablesdbdesign newsgroup will reveal that it is rarely
necessary to store a calculated value, particularly so if you can calculate
it "one the fly" in a query. That isn't to say "never", but "rarely". Can
you describe your situation a bit further...?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

If I want to perform a calculation I am using a control source of a field.
the problem is I want to store that date in the table and it won't let me.
How do I store the calculated field in the table. My formula works here it is.
=DateAdd("m",[ServiceInterval],[tblLog Subform].[Form]![ServiceDate])
I want to store this date in the NextServiceDate field in my service

table.

 

[ruralguy via AccessMonster.com]

You need to use either the Current event of the MainForm or maybe the
AfterUpdate event of a control on the MainForm. At least that is my best
guess based on the information you have supplied so far. It is generally not
a good idea to store calculated values for a number of reasons but maybe this
is one of the exception cases.

If I want to perform a calculation I am using a control source of a field.
the problem is I want to store that date in the table and it won't let me.
How do I store the calculated field in the table. My formula works here it is.
=DateAdd("m",[ServiceInterval],[tblLog Subform].[Form]![ServiceDate])
I want to store this date in the NextServiceDate field in my service table.

 

[Calvin]

I am building a database that calculates the next service date. In order to
do this I need to store the last service date. So far everything is working
well but the problem is I need to put the calculation somewhere to do this.
So far i've been working in the form using the control source to perform the
calculation. I want the calculated date stored in the table but b/c I am
using the control source for the calculation it isn't storing the data. So if
I were to use and event how do I put this formula in so that it will work
when the form is opened.

Calvin

A scan of the tablesdbdesign newsgroup will reveal that it is rarely
necessary to store a calculated value, particularly so if you can calculate
it "one the fly" in a query. That isn't to say "never", but "rarely". Can
you describe your situation a bit further...?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

If I want to perform a calculation I am using a control source of a field.
the problem is I want to store that date in the table and it won't let me.
How do I store the calculated field in the table. My formula works here it is.
=DateAdd("m",[ServiceInterval],[tblLog Subform].[Form]![ServiceDate])
I want to store this date in the NextServiceDate field in my service

table.

 

[Douglas J. Steele]

Rather than doing the calculation as the control source of the control on
your form, do it as a computed field in a query, and use that query as the
form's RecordSource.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I am building a database that calculates the next service date. In order to
do this I need to store the last service date. So far everything is
working
well but the problem is I need to put the calculation somewhere to do
this.
So far i've been working in the form using the control source to perform
the
calculation. I want the calculated date stored in the table but b/c I am
using the control source for the calculation it isn't storing the data. So
if
I were to use and event how do I put this formula in so that it will work
when the form is opened.

Calvin

A scan of the tablesdbdesign newsgroup will reveal that it is rarely
necessary to store a calculated value, particularly so if you can
calculate
it "one the fly" in a query. That isn't to say "never", but "rarely".
Can
you describe your situation a bit further...?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

If I want to perform a calculation I am using a control source of a
field.
the problem is I want to store that date in the table and it won't let
me.
How do I store the calculated field in the table. My formula works here
it is.
=DateAdd("m",[ServiceInterval],[tblLog Subform].[Form]![ServiceDate])
I want to store this date in the NextServiceDate field in my service

table.

 

[John W. Vinson]

I am building a database that calculates the next service date. In order to
do this I need to store the last service date. So far everything is working
well but the problem is I need to put the calculation somewhere to do this.
So far i've been working in the form using the control source to perform the
calculation. I want the calculated date stored in the table but b/c I am
using the control source for the calculation it isn't storing the data. So if
I were to use and event how do I put this formula in so that it will work
when the form is opened.

As Jeff says, it's probably not necessary to store the date. If you can
calculate it on the form, you can calculate it in a Query, on a different
form, in a report - pretty much wherever!

Can you explain how the calculation is done, and what benefit you get from
storing it in a Table that you can't get from calculating it again as needed?

John W. Vinson [MVP]

 

[David W. Fenton]

Rather than doing the calculation as the control source of the
control on your form, do it as a computed field in a query, and
use that query as the form's RecordSource.

If you're not using the value for filtering or sorting, I'd say to
*not* put it in the query/recordsource, but put it in the
controlsource. The reason is that the calculation won't happen until
you load the requested record(s), rather than as soon as Rushmore
finishes populating the recordset. This is particularly relevant for
reports, where there is no need whatsoever for calculated fields
anywhere except in controls *unless* your report logic somehow
depends on using that value for something other than display.

 

[Douglas J. Steele]

If you're not using the value for filtering or sorting, I'd say to
*not* put it in the query/recordsource, but put it in the
controlsource. The reason is that the calculation won't happen until
you load the requested record(s), rather than as soon as Rushmore
finishes populating the recordset. This is particularly relevant for
reports, where there is no need whatsoever for calculated fields
anywhere except in controls *unless* your report logic somehow
depends on using that value for something other than display.

I don't disagree with that, David.

However, it sounded as though Calvin wanted the data available elsewhere (or
why else would he be concerned that the calculation wasn't being saved?) By
using a query, he'd have a compromise.

 

[David W. Fenton]

I don't disagree with that, David.

However, it sounded as though Calvin wanted the data available
elsewhere (or why else would he be concerned that the calculation
wasn't being saved?) By using a query, he'd have a compromise.

I agree that if you're using the QueryDef in multiple locations and
need the calculated value in multiple locations then, yes, it makes
sense to put it in the QueryDef.

But as a general principle, I would put calculations in the last
level, i.e., the form or report controls.