Auto-Update for dates

[style214]

I am still fairly new to building Access databases, and I have ran into
a problem I cannot find any information on.

I have a database that houses several different pieces of data, and I
am trying to set it up so certain information is automatically updated.


The first one:

I want to pull a date from one field insp_date, when a radio button is
checked, and add 1 month to it, and drop that date into another column,
grndchk_date. A coworker suggested doing this via the form, but I do
not know how to do this. I only want this to occur if the radio button
is checked. I currently have the field set to a Yes/No option.

The second one:

I want to pull the same inspection date, and output to a separate field
based off of one of 3 options. These options would add either 90 days,
1 year, or 3 years.

I would guess that if I can get a solution to the first one, I can
modify it to use it for the other 3 options. I am not a complete expert
on this, so any help would be greatly appreciated. If there is any
further information needed, please let me know.

 

[Guest]

Check the Dateadd() function. It will do the tasks you outlined

 

[Vande]

Can it be set to automatically do the update and add the information to
the field in the database from the form?

 

[Joseph Meehan]

I am still fairly new to building Access databases, and I have ran into
a problem I cannot find any information on.

I have a database that houses several different pieces of data, and I
am trying to set it up so certain information is automatically
updated.


The first one:

I want to pull a date from one field insp_date, when a radio button is
checked, and add 1 month to it, and drop that date into another
column, grndchk_date. A coworker suggested doing this via the form,
but I do not know how to do this. I only want this to occur if the
radio button is checked. I currently have the field set to a Yes/No
option.

The second one:

I want to pull the same inspection date, and output to a separate
field based off of one of 3 options. These options would add either
90 days, 1 year, or 3 years.

I would guess that if I can get a solution to the first one, I can
modify it to use it for the other 3 options. I am not a complete
expert on this, so any help would be greatly appreciated. If there is
any further information needed, please let me know.

I don't think you want to do that. Really.

Normally you don't want to store the result of a calculation. Rather
than storing a date one month in advance, you just re-calculate it any time
you need it in a form, report or query. Access works better calculating it
than looking it up.

There are two possible problems that might make one way or the other the
best.

If the first date may change you need to decide if the second needs to
change as well. If you want the second to change along with the first, then
DO NOT save it to a new field, just recompute it. If you want the second
date to remain the same, even if the first one changes, then you will need
to create the second date. You can do with on a form or query or several
other choice, but you will need an event that will trigger the action and
this can't be done in the table, but it can be done in a form. You will
need to make sure that all users enter the data in that form. This is
another good reason to computer it each time you need it and not to store
it.

 

[Vande]

I need the information in both output locations to update whenever the
original date is updated. The first one will only be used in specific
cases, but the second set, the 3 possible categories, are required for
all the entries in the database. There will be a limited number of
users accessing this database, and I am working to eliminate the human
factor, and also to use this a build up to a future addition that will
push tasks out to Outlook to automate scheduling inspections. I am
looking at the options on how to get the dateadd function to only run
when the radio button is checked, to see if I can do this simple, or if
I am going to have to try and build a Vbscript to do all of this.

 

[Joseph Meehan]

I need the information in both output locations to update whenever the
original date is updated. The first one will only be used in specific
cases, but the second set, the 3 possible categories, are required for
all the entries in the database. There will be a limited number of
users accessing this database, and I am working to eliminate the human
factor, and also to use this a build up to a future addition that will
push tasks out to Outlook to automate scheduling inspections.

It sure sounds to me like you do NOT want to store those dates. Just
compute them as needed. You can do this in queries, forms and reports.
Your users should never be looking at the data in a table anyway.

I am
looking at the options on how to get the dateadd function to only run
when the radio button is checked,

Why? If you are using it to display the dates on a form, query or
report, why not just allow it to run anytime the form, query or report is
run?

 

[Vande]

I will still need to find how to pull a date based off of a separate
fields data. By only running it in the queries, forms and reports, it
seems like it would end up with a lot of extra code, but with doing the
calculations in the actual input form, I would not have to worry about
it. My biggest problem is I still have not been able to get the code to
work just using the dateadd function...any suggestions?

 

[Joseph Meehan]

I will still need to find how to pull a date based off of a separate
fields data. By only running it in the queries, forms and reports, it
seems like it would end up with a lot of extra code,

Really more like repeating the same code. Access does tend to work
better that way and it often avoids problems.