Date calculation

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Okay, was looking for something that did a date calculation and I found it;
however, on the microsoft website the directions told me to put it in the
control field. If I do that then, it does not update the table that it came
from.

This is what I used =DateSerial(year([Active_Initial_106_2_Qual]),
month([Active_Initial_106_2_Qual])+6, day([Active_Initial_106_2_Qual])).

where can I put this statement for it to work. I have two calculation
fields the first one Active_Initial_106_2_Qual and the second
Current_Requal_date. I need the second date to calculate out 30 months. Can
you help
 
You shouldn't be storing calculated values anyhow. As fellow Access MVP John
Vinson likes to say "Storing calculated data generally accomplishes only
three things: it wastes disk space, it wastes time (a disk fetch is much
slower than almost any reasonable calculation), and it risks data validity,
since once it's stored in a table either the Total or one of the fields that
goes into the total may be changed, making the value WRONG."

Instead, add an additional field to your query that provides the computed
date.
 
If I do that then, it does not update the table that it came
from.
Click to expand...

It shouldn't do so.

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]
 
Ok, just so I understand it clearly.

I have a table and a form based on that table that has two date fields...one
with the initial date and the second with a calculated date field. You are
saying redo the calculation in the form in the control source field when i
add someone new to the table.

Can't I do a macro for this when someone new is added? How do I do that?
Do you have a website on macros or visual basic to suggest.

Thank you for your help!
 
No, that's not what John is saying at all.

Your table should only have 1 date field in it. You should have a query
based on that table that has the second date field in it as a calculated
field. You should use the query wherever you would otherwise have used your
table.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)
 
Ok, just so I understand it clearly.

I have a table and a form based on that table that has two date fields...one
with the initial date and the second with a calculated date field. You are
saying redo the calculation in the form in the control source field when i
add someone new to the table.
Click to expand...

No.

The field Current_Requal_date should simply NOT EXIST in your table.

Don't store it *anywhere*. You can calculate it whenever it's needed,
either in a calculated field in a Query, or as the Control Source of a
textbox on a Form or Report.

The only exception to this would be if you want the date to *default*
to 6 months (or 30 months, or whatever) after the first date, but you
want to be able to edit that date freely to any other value. If that's
the case post back.

John W. Vinson[MVP]
 
John,

My apologies, apparently, I am not being very clear on the objective of the
database. Here is goes..

I do not need the Current_Requal_date field...as I do need it for my
operations. Let me explain fully...

We have tech's who qualify on one date Current_106_2_Qual_Date and they will
need to requal at a later date (30 months) in the future second field
Current_Requal_date. My boss, the one who uses this database to see who has
qualified and when they will need to be qualified asked for the second field.
Now with that said, how do I get the form to calculate the extended date out
30 months for that particular field and then post it back to the table for
which that field feeds back.

Hopefully, this helps to clarify the situation and help you guys figure out
how to help me.

Thank you for any help on this matter.
 
John,

My apologies, apparently, I am not being very clear on the objective of the
database. Here is goes..

I do not need the Current_Requal_date field...as I do need it for my
operations. Let me explain fully...

We have tech's who qualify on one date Current_106_2_Qual_Date and they will
need to requal at a later date (30 months) in the future second field
Current_Requal_date. My boss, the one who uses this database to see who has
qualified and when they will need to be qualified asked for the second field.
Now with that said, how do I get the form to calculate the extended date out
30 months for that particular field and then post it back to the table for
which that field feeds back.

Hopefully, this helps to clarify the situation and help you guys figure out
how to help me.
Click to expand...

Your boss needs to SEE the requaldate.

That does *NOT* mean that you need to STORE the requaldate.

Data storage and data display are *two separate operations*; driving
data storage design by the needs of data display can be very risky.

If your boss is looking at the table datasheet... train your boss, and
provide him or her with the tools (correctly designed forms) to make
it unnecessary to do so.

You can put a textbox on the Form, set its Control Source to

=DateAdd("m", 30, [Current_106_2_Qual_Date])

and that textbox will display the correct requalification date. You
can put a similar textbox on any other Form or any Report, to display
that same information.

Can you explain WHY (other than your getting bad advice from your
boss) it is essential to store the date in your table? What good does
it do you? Is it worth the risk that this date could be edited, making
it WRONG? Is it worth the risk that an error could be corrected in the
Qual_Date, leaving the (stored) requalification date in the table,
knowing that it is WRONG? What business need is served by deliberately
using an incorrect program design?

Oh, it CAN be done. And you can google these groups to find out how,
or if you insist, one of us will tell you how. I'm just trying to
persuade you that it is neither necessary to do so to accomplish your
valid business need, nor is it prudent to do so.

John W. Vinson[MVP]
 
John,

Okay, great advice...I understand your frustration with me as I am very new
at this and am only going by the guidelines set forth for me through you
guessed it....my boss.

Now that I understand that the date does not need to be stored in a table, I
have used the calculation that you have provided me! Thank you!

The problem was that my boss liked to look at the tables....there in lies
the problems with it all. Mea Culpa.

John Vinson said:
John,

My apologies, apparently, I am not being very clear on the objective of the
database. Here is goes..

I do not need the Current_Requal_date field...as I do need it for my
operations. Let me explain fully...

We have tech's who qualify on one date Current_106_2_Qual_Date and they will
need to requal at a later date (30 months) in the future second field
Current_Requal_date. My boss, the one who uses this database to see who has
qualified and when they will need to be qualified asked for the second field.
Now with that said, how do I get the form to calculate the extended date out
30 months for that particular field and then post it back to the table for
which that field feeds back.

Hopefully, this helps to clarify the situation and help you guys figure out
how to help me.
Click to expand...

Your boss needs to SEE the requaldate.

That does *NOT* mean that you need to STORE the requaldate.

Data storage and data display are *two separate operations*; driving
data storage design by the needs of data display can be very risky.

If your boss is looking at the table datasheet... train your boss, and
provide him or her with the tools (correctly designed forms) to make
it unnecessary to do so.

You can put a textbox on the Form, set its Control Source to

=DateAdd("m", 30, [Current_106_2_Qual_Date])

and that textbox will display the correct requalification date. You
can put a similar textbox on any other Form or any Report, to display
that same information.

Can you explain WHY (other than your getting bad advice from your
boss) it is essential to store the date in your table? What good does
it do you? Is it worth the risk that this date could be edited, making
it WRONG? Is it worth the risk that an error could be corrected in the
Qual_Date, leaving the (stored) requalification date in the table,
knowing that it is WRONG? What business need is served by deliberately
using an incorrect program design?

Oh, it CAN be done. And you can google these groups to find out how,
or if you insist, one of us will tell you how. I'm just trying to
persuade you that it is neither necessary to do so to accomplish your
valid business need, nor is it prudent to do so.

John W. Vinson[MVP]
Click to expand...
 
The problem was that my boss liked to look at the tables....there in lies
the problems with it all. Mea Culpa.
Click to expand...

Nope - sui culpa! <g>

Feel free to have him contact me directly if he needs more
confirmation than your good advice.

John W. Vinson[MVP]
 
One more time...

In speaking to my boss, he has now asked for a report for the requal date
from that field. Because it is a calculated field now, I can't do a query
from it, nor a report based on it. How do I pull this information into a
report if it is not stored anywhere.

In addition...We have an AS400 database that runs this function. That is
the reason why he is asking for this type of report. It serves us on a daily
basis of acurate information; however, it will be going away here very soon,
within the next year. That is why I am trying to do just a departmentental
db to have the same functionality. That function described above will be one
of the things we want it to do.

Is there anyway to do this with the calculated field? What I am looking for
is this...

A query that pulls that date field, with name, emp num, etc. The criteria
will be Within 1, 2, 3, 6 months who will need to be requalified intheir job.
Then base a report on that query. Or maybe just a report from that data in
the table with that one field pulled in somehow.

Thank you for your help in this matter.
 
I haven't been following this thread too closely, but unless the calculation
is horrendously complicated, you can calculate the date in a query and use
that query as the RecordSource for both your form and your report.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
Is there anyway to do this with the calculated field? What I am looking for
is this...
Click to expand...

Yes. Do the calculation IN THE QUERY.

Create a Query based on your table. Include any other fields you want
to see.

In a vacant Field cell in the query grid type

RequalDate: DateAdd("m", 30, [datefield])

or whatever expression correctly calculates the date that you want.
You can have several of these fields if you wish.

This Query will now look, act, work just like a Table; it will have a
field named RequalDate, which can be sorted, searched, reported,
filtered, ANYTHING you wish to do with a table field (except edit its
value of course, since it's calculated). The Query can be used as the
basis for a Form or a Report, or exported to an external file...
*just* like a table.


John W. Vinson[MVP]
 
Thank you, thank you, thank you! You are great! Now that I understand what
I need to do to get the report or whatever from a query, I get it. I could
not have done this without your help and constant guidance! Whomever is your
boss, tell them to give you a raise. If I had the money, or when I get rich
from your help, I'll give it too you! Oh, maybe I should not have said that
on paper! Anyway, Again, THANK YOU!

JudyT

John Vinson said:
Is there anyway to do this with the calculated field? What I am looking for
is this...
Click to expand...

Yes. Do the calculation IN THE QUERY.

Create a Query based on your table. Include any other fields you want
to see.

In a vacant Field cell in the query grid type

RequalDate: DateAdd("m", 30, [datefield])

or whatever expression correctly calculates the date that you want.
You can have several of these fields if you wish.

This Query will now look, act, work just like a Table; it will have a
field named RequalDate, which can be sorted, searched, reported,
filtered, ANYTHING you wish to do with a table field (except edit its
value of course, since it's calculated). The Query can be used as the
basis for a Form or a Report, or exported to an external file...
*just* like a table.


John W. Vinson[MVP]
Click to expand...
 
Okay, now the report that they want to see it this.

From the calculated field of RequalDate we want to show people who will need
to be requaled within 1, 3, and 6 month time periods. There are several
things that have to be accomplished prior to these people becoming requaled.
It is important to know prior to the dates.

I did a search and came up with "Microsoft's website", for DateDiff,
DatePart. Which one would be better to use and how do I use it?

Thank you!
Judy

John Vinson said:
Is there anyway to do this with the calculated field? What I am looking for
is this...
Click to expand...

Yes. Do the calculation IN THE QUERY.

Create a Query based on your table. Include any other fields you want
to see.

In a vacant Field cell in the query grid type

RequalDate: DateAdd("m", 30, [datefield])

or whatever expression correctly calculates the date that you want.
You can have several of these fields if you wish.

This Query will now look, act, work just like a Table; it will have a
field named RequalDate, which can be sorted, searched, reported,
filtered, ANYTHING you wish to do with a table field (except edit its
value of course, since it's calculated). The Query can be used as the
basis for a Form or a Report, or exported to an external file...
*just* like a table.


John W. Vinson[MVP]
Click to expand...
 
Thank you, thank you, thank you! You are great! Now that I understand what
I need to do to get the report or whatever from a query, I get it. I could
not have done this without your help and constant guidance! Whomever is your
boss, tell them to give you a raise. If I had the money, or when I get rich
from your help, I'll give it too you! Oh, maybe I should not have said that
on paper! Anyway, Again, THANK YOU!
Click to expand...

My boss (the tabby cat with the short fur) is sitting on my lap
purring, and she's not a bit impressed. I'm self-employed.

I appreciate the kind words though!

John W. Vinson[MVP]
 
Okay, now the report that they want to see it this.

From the calculated field of RequalDate we want to show people who will need
to be requaled within 1, 3, and 6 month time periods. There are several
things that have to be accomplished prior to these people becoming requaled.
It is important to know prior to the dates.
Click to expand...

Do you want three separate queries - one month, three month, six
month?

Or one query showing those people requaling in the next month, in the
third-through-fourth month, or the sixth-through-seventh?

I'm not certain what you want to see, exactly, but just as an example,
you can show all the people whose requal date is within the next month
using a Query with a criterion on the calculated QualDate field of
= Date() AND < DateAdd("m", 1, Date())
Click to expand...

Change the 1 to 3, or 6, to show people in the next three or six
months respectively.

John W. Vinson[MVP]
 
Hello John,

How are you today?

The formula works great for the separation of who needs to be qualified
within 1 month, then 2 months; however, I need to not have the 1 month not
included in the 2 months and 2 months not included in 3 months respectively.
Is that possible?

Thank you.
Judy
 
Sorry, let me rephrase the question and situation...
The formula works great for the separation of who needs to be qualified
within 1 month, then 2 months; however, I do not need to not have the 1 month
included in the 2 month report and so on.
Click to expand...
 
Sorry, let me rephrase the question and situation...
Click to expand...

please do...

Sure, you can apply a date range criterion.

Which dates SHOULD be included in the two month, in the six month?

John W. Vinson[MVP]
 
Back
Top