Needing some access help

  • Thread starter Thread starter mears
  • Start date Start date
M

mears

Hi I am new to access. I have never had any formal training, I am self
taught. I have a table that reflects two dates. I want to do a calculation
in the table that reflects the differences between the two.

Now, I did this in a form where I used the datedif formula and I expected it
to write back to the table but it does not. I have spent a lot of time on
this and cannot figure it out. Can it be done?

Please note that I am a novice at access so if you can keep it simple for
me.

Thanks

Mike
 
Hi Mike,

We do this using an additional calculated field in a query.
Open a new query and place your table there.
Now place both date fields and in the third column enter:
Difference:[datefield2]-[datefield1]
Replace the [Datefield1] and [Datefield2] with your fieldnames and you'll
see the number of days difference when you save and run the query.

Nic;o)
 
Hi I am new to access. I have never had any formal training, I am self
taught. I have a table that reflects two dates. I want to do a calculation
in the table that reflects the differences between the two.

Now, I did this in a form where I used the datedif formula and I expected it
to write back to the table but it does not. I have spent a lot of time on
this and cannot figure it out. Can it be done?
Click to expand...

It can... but it shouldn't.

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.


John W. Vinson[MVP]
 
Back
Top