Store a calculated field in a table

[Bill]

I am using DateDiff to calculate the number of days
between 2 date fields on a form, and want to store the
results of this function in another field in the table
record. Any help greatly appreciated. Thanks.

 

[Wayne Morgan]

First, don't. It is bad practice to store data you can calculate. Just
calculate it when you need it. If you believe you must, create a hidden
textbox on the form. Bind this textbox to the field in the table. In the
form's BeforeUpdate event, set the value of the hidden textbox to the value
of the calculated textbox.

 

[John Vinson]

I am using DateDiff to calculate the number of days
between 2 date fields on a form, and want to store the
results of this function in another field in the table
record. Any help greatly appreciated. Thanks.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

 

[Vikesh Singh]

Hi. I need to update the field in the table. I have
tried what you have suggested and there seems to be no
change. What am I doing wrong?

Much appreciated.

Vikesh Singh - Durban, South Africa

 

[Wayne Morgan]

What field are you needing to update and what are you trying to do in order
to do this?

 

[Guest]

Hi.

I have a table setup with the following fields:

Emp. Id | Certificate Expiry Date | Days left|

I cannot calculate the days left until expiry on the
table. I can however do this on the form. I need to take
the value from the form and append it to the table field
Days Left. How should I do this or can I do this another
way. At the end of it all, I need to know which employees
certificate is going to apply in say 10 days or x days.

Thanks.

 

[Evi]

You say you 'cannot calculate the days left until expiry on the table'. Do
you mean that you cannot calculate the days left until the expiry date has
been entered into the table?

You could, of course, perform this calculation is a query but I can't
imagine why you would want to store it in a table. As soon as a day passes,
your information would surely be incorrect. Today the certificate has 10
days left, tomorrow it will have 9 days left so the data you entered
yesterday would be wrong.

In your query you can, of course, have
DaysLeft: Date()-[Certificate Expiry Date]

If, for some reason, you had to enter this into the table then you would
have to have the DaysLeft field in your table and use an update query.


Put your calculated query (we'll call it QryDaysLeft) into the query, as
well as the table. Join the two by the Emp. ID field

Put the DaysLeft field from the table into the grid. In the Update To row
put

QryDaysLeft!DaysLeft

Evi

 

[Wayne Morgan]

You're right, you can't do a calculation in a table. You have to use a
query, form, or report to do the calculation. Using a form, you could then
have the result of that calculation stored back in the table, but there is
no need to. You can simply make the calculation anytime you need it. This is
the recommended way to do it. Don't store any data that you don't need. If
it can be calculated, it's not needed.

 

[Vikesh Singh]

Thanks very much - it makes perfect sense to use a query.

Much appreciated.

-----Original Message-----
You say you 'cannot calculate the days left until expiry on the table'. Do
you mean that you cannot calculate the days left until the expiry date has
been entered into the table?

You could, of course, perform this calculation is a query but I can't
imagine why you would want to store it in a table. As soon as a day passes,
your information would surely be incorrect. Today the certificate has 10
days left, tomorrow it will have 9 days left so the data you entered
yesterday would be wrong.

In your query you can, of course, have
DaysLeft: Date()-[Certificate Expiry Date]

If, for some reason, you had to enter this into the table then you would
have to have the DaysLeft field in your table and use an update query.


Put your calculated query (we'll call it QryDaysLeft) into the query, as
well as the table. Join the two by the Emp. ID field

Put the DaysLeft field from the table into the grid. In the Update To row
put

QryDaysLeft!DaysLeft

Evi

Hi.

I have a table setup with the following fields:

Emp. Id | Certificate Expiry Date | Days left|

I cannot calculate the days left until expiry on the
table. I can however do this on the form. I need to take
the value from the form and append it to the table field
Days Left. How should I do this or can I do this another
way. At the end of it all, I need to know which employees
certificate is going to apply in say 10 days or x days.

Thanks.

.