Saving Calculations to A Table

[debi.robarts]

Ok, in my database I have something like this:

Date One
# of Days to Next Date

These calculate a field "Date Two."

Because "Date Two" is an expression (calculated by the form), I can't
set the control source to save to a field in my table. How can I save
that value to my table?

Does this make sense?

Thank you!
Debi

[debi.robarts]

I realized that I need to use VBA to make this happen.

I have "Date Two" and I'm creating a new hidden field "Date Two Print"
- this is the one that will show up on my database, right?

So.. in the properties of "Date Two Print" I went to AfterUpdate.. and
I know there is something I put there to copy whatever is in "Date Two"
and paste it into "Date Two Print" .. problem is, I don't know what! :)

Any help is greatly appreciated!!

de**********@gm ail.com wrote:

Ok, in my database I have something like this:

Date One
# of Days to Next Date

These calculate a field "Date Two."

Because "Date Two" is an expression (calculated by the form), I can't
set the control source to save to a field in my table. How can I save
that value to my table?

Does this make sense?

Thank you!
Debi

[Lyle Fairfield]

de**********@gm ail.com wrote:

Ok, in my database I have something like this:

Date One
# of Days to Next Date

These calculate a field "Date Two."

Because "Date Two" is an expression (calculated by the form), I can't
set the control source to save to a field in my table. How can I save
that value to my table?

Does this make sense?

No, it does not make sense.

Unless the caclulation is extremely demanding in terms of time and
resources, which seems unlikely in this case, it is inefficient to
store the results of calculations in a table.

Instead, when we need the calculated result we use a Select Query to do
our calculations and return our calculated field:

Yours is likely to be something like:

SELECT DateOne, DateAdd("d", NumberofDays, DateOne) AS DateTwo FROM
Table1

(Probably you should avoid calling a field "# of Days" as Access/JET
and others are particular about Field Names. It may be efficient to use
only AlphaNumeric characters and the underscore, and to begin all such
names with an Alpha character.

[debi.robarts]

I need the date two stored in my table because that is the date I am
trying to pull up in a query of when this record needs to be looked at
again. For example:

Date one is when the lease commenced
# of days is how many days that lease lasts

Say date two is when the lease needs to be renewed - and an action
needs to be taken by our team. I need the table to save that date so
when I do a query of all "date two" fields that are within the next 60
days, it'll pull up each record.

I need this date 1) to show up on the database so when someone is
viewing the record they can see it and B) to save to my main table so
it can be queried.

With your solution, will this all still work?

I'm still a little new to Access so I don't know nearly as much as
others. :) Thank you for your help!!
Lyle Fairfield wrote:

de**********@gm ail.com wrote:

Ok, in my database I have something like this:

Date One
# of Days to Next Date

These calculate a field "Date Two."

Because "Date Two" is an expression (calculated by the form), I can't
set the control source to save to a field in my table. How can I save
that value to my table?

Does this make sense?

No, it does not make sense.

Unless the caclulation is extremely demanding in terms of time and
resources, which seems unlikely in this case, it is inefficient to
store the results of calculations in a table.

Instead, when we need the calculated result we use a Select Query to do
our calculations and return our calculated field:

Yours is likely to be something like:

SELECT DateOne, DateAdd("d", NumberofDays, DateOne) AS DateTwo FROM
Table1

(Probably you should avoid calling a field "# of Days" as Access/JET
and others are particular about Field Names. It may be efficient to use
only AlphaNumeric characters and the underscore, and to begin all such
names with an Alpha character.

[Lyle Fairfield]

de**********@gm ail.com wrote:

I need the date two stored in my table because that is the date I am
trying to pull up in a query of when this record needs to be looked at
again. For example:

Date one is when the lease commenced
# of days is how many days that lease lasts

Say date two is when the lease needs to be renewed - and an action
needs to be taken by our team. I need the table to save that date so
when I do a query of all "date two" fields that are within the next 60
days, it'll pull up each record.

SELECT
DetailsorWhatev er,
DateOne AS Commenced,
DateAdd("d",Day s,DateOne) AS Due
FROM Table1
WHERE
DateAdd("d",Day s,DateOne)>Date ()
AND
DateAdd("d",Day s,DateOne)<Date Add("d",61,Date ());
ORDER BY
DateAdd("d",Day s,DateOne)