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

Saving Calculations to A Table

P: n/a
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

Dec 5 '06 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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**********@gmail.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
Dec 5 '06 #2

P: n/a
de**********@gmail.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.

Dec 5 '06 #3

P: n/a
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**********@gmail.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.
Dec 5 '06 #4

P: n/a
de**********@gmail.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
DetailsorWhatever,
DateOne AS Commenced,
DateAdd("d",Days,DateOne) AS Due
FROM Table1
WHERE
DateAdd("d",Days,DateOne)>Date()
AND
DateAdd("d",Days,DateOne)<DateAdd("d",61,Date());
ORDER BY
DateAdd("d",Days,DateOne)

Dec 5 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.