Calculating a date field based on another field's date, based on another fields value

[circuit slave]

Can someone tell me if this is possible? I have three fields:

I want the field [Expires] to update automatically by one year, based
on the date of the date field [Date], but only if the second field's
value [CertCode] has a certain value. Let me explain:

I have certification dates which expire one year from date of
certification. But not all certification's have expirations. The field
[CertCode] has a drop down box with a value of 1 thru various numbers
up to 950. Codes 911 to 950 are non-expiring. So, I'd like to leave
the [Expires] field blank when these values are chosen.

The way I have it set up now is:

Properties "All" tab and Event Procedure , On Click
[Expires] = DateAdd("yyyy", 1, [Date])

But all this formula does when you click the field is auto inputs a
one-year expiration, regardless of what is selected in the CertCode
field.

Any suggestions?

thanks in advance

 

[Allen Browne]

For the certificates that expire, will they *always* expire exactly 1 period
(year) after issued? Or could there be cases where you need to edit an
certificate so it is issued just for a week or 3 months, though they
normally expire a year later?

If it is always 1 period after issue, you must not store the expiry in your
table. Instead, just calcuate the expiry when needed, and your database
cannot be wrong.

If expiry could validly be different from the issue date plus one period,
then you do need the Expires date in your table, and you would use the
AfterUpdate event of the issue date to set the expiry date.

More detail and examples in:
Calculated Fields
at:
http://allenbrowne.com/casu-14.html

(BTW, don't use Date as a field name. That's a reserved word in VBA (for the
system date), so the ambiguity will bite you. Perhaps IssueDate.)

 

[circuit slave]

Thanks for the info, Allen. Actually, this is a form that I'm speaking
of, not the actual table.

These fields have already been set, but I'm just trying to see if there
is a way to auto populate an expiration one-year from the date.

And these certifications always need to be redone exactly one year from
date entered.

For the certificates that expire, will they *always* expire exactly 1 period
(year) after issued? Or could there be cases where you need to edit an
certificate so it is issued just for a week or 3 months, though they
normally expire a year later?

If it is always 1 period after issue, you must not store the expiry in your
table. Instead, just calcuate the expiry when needed, and your database
cannot be wrong.

If expiry could validly be different from the issue date plus one period,
then you do need the Expires date in your table, and you would use the
AfterUpdate event of the issue date to set the expiry date.

More detail and examples in:
Calculated Fields
at:
http://allenbrowne.com/casu-14.html

(BTW, don't use Date as a field name. That's a reserved word in VBA (for the
system date), so the ambiguity will bite you. Perhaps IssueDate.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Can someone tell me if this is possible? I have three fields:

I want the field [Expires] to update automatically by one year, based
on the date of the date field [Date], but only if the second field's
value [CertCode] has a certain value. Let me explain:

I have certification dates which expire one year from date of
certification. But not all certification's have expirations. The field
[CertCode] has a drop down box with a value of 1 thru various numbers
up to 950. Codes 911 to 950 are non-expiring. So, I'd like to leave
the [Expires] field blank when these values are chosen.

The way I have it set up now is:

Properties "All" tab and Event Procedure , On Click
[Expires] = DateAdd("yyyy", 1, [Date])

But all this formula does when you click the field is auto inputs a
one-year expiration, regardless of what is selected in the CertCode
field.

Any suggestions?

thanks in advance

 

[Allen Browne]

If you have permission to do so, remove the epiration date field from the
table. If you need to beg or plead with someone to do it, explain that this
field breaks one of the most basic rule of data normalization, and that if
they do not allow you to remove it, you will not be responsible for the bad
data that will result from storing this redundant field. And you will be
very happy to say, "I told you so" in days to come. Whenever the problem
arises. Because *they* did not let you apply the correct solution, so it
will be *their* fault. Repeatedly. Until one day they give up and let you
use a correct design.

It is so easy to create the query with the calculated field that can never
be wrong. Then set the RecordSource property of your form to this query, so
you have the expiration date in your form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the info, Allen. Actually, this is a form that I'm speaking
of, not the actual table.

These fields have already been set, but I'm just trying to see if there
is a way to auto populate an expiration one-year from the date.

And these certifications always need to be redone exactly one year from
date entered.

For the certificates that expire, will they *always* expire exactly 1
period
(year) after issued? Or could there be cases where you need to edit an
certificate so it is issued just for a week or 3 months, though they
normally expire a year later?

If it is always 1 period after issue, you must not store the expiry in
your
table. Instead, just calcuate the expiry when needed, and your database
cannot be wrong.

If expiry could validly be different from the issue date plus one period,
then you do need the Expires date in your table, and you would use the
AfterUpdate event of the issue date to set the expiry date.

More detail and examples in:
Calculated Fields
at:
http://allenbrowne.com/casu-14.html

(BTW, don't use Date as a field name. That's a reserved word in VBA (for
the
system date), so the ambiguity will bite you. Perhaps IssueDate.)

Can someone tell me if this is possible? I have three fields:

I want the field [Expires] to update automatically by one year, based
on the date of the date field [Date], but only if the second field's
value [CertCode] has a certain value. Let me explain:

I have certification dates which expire one year from date of
certification. But not all certification's have expirations. The field
[CertCode] has a drop down box with a value of 1 thru various numbers
up to 950. Codes 911 to 950 are non-expiring. So, I'd like to leave
the [Expires] field blank when these values are chosen.

The way I have it set up now is:

Properties "All" tab and Event Procedure , On Click
[Expires] = DateAdd("yyyy", 1, [Date])

But all this formula does when you click the field is auto inputs a
one-year expiration, regardless of what is selected in the CertCode
field.

 

[circuit slave]

Thanks for more info, Allen, and yes---I can change it---I'm not sure
who created the form, but thanks, I have the authority to rename this
field.

Now, how do I implement the feature, so it WILL NOT update the
expiration automatically for non-expiring certifications, and that
field will remain blank?

Remember, there are certain cert codes from the pull down combo box
which never expire and are just training.

thanks

If you have permission to do so, remove the epiration date field from the
table. If you need to beg or plead with someone to do it, explain that this
field breaks one of the most basic rule of data normalization, and that if
they do not allow you to remove it, you will not be responsible for the bad
data that will result from storing this redundant field. And you will be
very happy to say, "I told you so" in days to come. Whenever the problem
arises. Because *they* did not let you apply the correct solution, so it
will be *their* fault. Repeatedly. Until one day they give up and let you
use a correct design.

It is so easy to create the query with the calculated field that can never
be wrong. Then set the RecordSource property of your form to this query, so
you have the expiration date in your form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the info, Allen. Actually, this is a form that I'm speaking
of, not the actual table.

These fields have already been set, but I'm just trying to see if there
is a way to auto populate an expiration one-year from the date.

And these certifications always need to be redone exactly one year from
date entered.

For the certificates that expire, will they *always* expire exactly 1
period
(year) after issued? Or could there be cases where you need to edit an
certificate so it is issued just for a week or 3 months, though they
normally expire a year later?

If it is always 1 period after issue, you must not store the expiry in
your
table. Instead, just calcuate the expiry when needed, and your database
cannot be wrong.

If expiry could validly be different from the issue date plus one period,
then you do need the Expires date in your table, and you would use the
AfterUpdate event of the issue date to set the expiry date.

More detail and examples in:
Calculated Fields
at:
http://allenbrowne.com/casu-14.html

(BTW, don't use Date as a field name. That's a reserved word in VBA (for
the
system date), so the ambiguity will bite you. Perhaps IssueDate.)

Can someone tell me if this is possible? I have three fields:

I want the field [Expires] to update automatically by one year, based
on the date of the date field [Date], but only if the second field's
value [CertCode] has a certain value. Let me explain:

I have certification dates which expire one year from date of
certification. But not all certification's have expirations. The field
[CertCode] has a drop down box with a value of 1 thru various numbers
up to 950. Codes 911 to 950 are non-expiring. So, I'd like to leave
the [Expires] field blank when these values are chosen.

The way I have it set up now is:

Properties "All" tab and Event Procedure , On Click
[Expires] = DateAdd("yyyy", 1, [Date])

But all this formula does when you click the field is auto inputs a
one-year expiration, regardless of what is selected in the CertCode
field.

 

[Allen Browne]

The idea it to *remove* the field from the table (not merely to rename it.)

I assume you have a lookup table of the types of certificate available. This
table will supply records to the combo box where the user can select a
certificate type. In this table, you will have a yes/no field indicating if
the certificate expires or not. I will assume this field is called NoExpiry,
and checked for the certificates that never expire.

Now create a query that uses both tables (the table of actual certificates
issued, and the table of certificate types.) In a fresh column in the Field
row, enter this expression:
Expiry: CVDate(IIf([NoExpiry], Null, DateAdd("yyyy",1, [DateIssued]))
You can now use this query (rather than the table) anywhere you need the
expiry date.

It's that easy.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for more info, Allen, and yes---I can change it---I'm not sure
who created the form, but thanks, I have the authority to rename this
field.

Now, how do I implement the feature, so it WILL NOT update the
expiration automatically for non-expiring certifications, and that
field will remain blank?

Remember, there are certain cert codes from the pull down combo box
which never expire and are just training.

thanks

If you have permission to do so, remove the epiration date field from the
table. If you need to beg or plead with someone to do it, explain that
this
field breaks one of the most basic rule of data normalization, and that
if
they do not allow you to remove it, you will not be responsible for the
bad
data that will result from storing this redundant field. And you will be
very happy to say, "I told you so" in days to come. Whenever the problem
arises. Because *they* did not let you apply the correct solution, so it
will be *their* fault. Repeatedly. Until one day they give up and let you
use a correct design.

It is so easy to create the query with the calculated field that can
never
be wrong. Then set the RecordSource property of your form to this query,
so
you have the expiration date in your form.

Thanks for the info, Allen. Actually, this is a form that I'm speaking
of, not the actual table.

These fields have already been set, but I'm just trying to see if there
is a way to auto populate an expiration one-year from the date.

And these certifications always need to be redone exactly one year from
date entered.


Allen Browne wrote:
For the certificates that expire, will they *always* expire exactly 1
period
(year) after issued? Or could there be cases where you need to edit an
certificate so it is issued just for a week or 3 months, though they
normally expire a year later?

If it is always 1 period after issue, you must not store the expiry in
your
table. Instead, just calcuate the expiry when needed, and your
database
cannot be wrong.

If expiry could validly be different from the issue date plus one
period,
then you do need the Expires date in your table, and you would use the
AfterUpdate event of the issue date to set the expiry date.

More detail and examples in:
Calculated Fields
at:
http://allenbrowne.com/casu-14.html

(BTW, don't use Date as a field name. That's a reserved word in VBA
(for
the
system date), so the ambiguity will bite you. Perhaps IssueDate.)

Can someone tell me if this is possible? I have three fields:

I want the field [Expires] to update automatically by one year,
based
on the date of the date field [Date], but only if the second field's
value [CertCode] has a certain value. Let me explain:

I have certification dates which expire one year from date of
certification. But not all certification's have expirations. The
field
[CertCode] has a drop down box with a value of 1 thru various
numbers
up to 950. Codes 911 to 950 are non-expiring. So, I'd like to
leave
the [Expires] field blank when these values are chosen.

The way I have it set up now is:

Properties "All" tab and Event Procedure , On Click
[Expires] = DateAdd("yyyy", 1, [Date])

But all this formula does when you click the field is auto inputs a
one-year expiration, regardless of what is selected in the CertCode
field.

 

[circuit slave]

I see. I understand, now. Thanks so much for taking the time. I'll
also check your site for further info that may help. Thanks again !!!!

The idea it to *remove* the field from the table (not merely to rename it.)

I assume you have a lookup table of the types of certificate available. This
table will supply records to the combo box where the user can select a
certificate type. In this table, you will have a yes/no field indicating if
the certificate expires or not. I will assume this field is called NoExpiry,
and checked for the certificates that never expire.

Now create a query that uses both tables (the table of actual certificates
issued, and the table of certificate types.) In a fresh column in the Field
row, enter this expression:
Expiry: CVDate(IIf([NoExpiry], Null, DateAdd("yyyy",1, [DateIssued]))
You can now use this query (rather than the table) anywhere you need the
expiry date.

It's that easy.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for more info, Allen, and yes---I can change it---I'm not sure
who created the form, but thanks, I have the authority to rename this
field.

Now, how do I implement the feature, so it WILL NOT update the
expiration automatically for non-expiring certifications, and that
field will remain blank?

Remember, there are certain cert codes from the pull down combo box
which never expire and are just training.

thanks

If you have permission to do so, remove the epiration date field from the
table. If you need to beg or plead with someone to do it, explain that
this
field breaks one of the most basic rule of data normalization, and that
if
they do not allow you to remove it, you will not be responsible for the
bad
data that will result from storing this redundant field. And you will be
very happy to say, "I told you so" in days to come. Whenever the problem
arises. Because *they* did not let you apply the correct solution, so it
will be *their* fault. Repeatedly. Until one day they give up and let you
use a correct design.

It is so easy to create the query with the calculated field that can
never
be wrong. Then set the RecordSource property of your form to this query,
so
you have the expiration date in your form.

Thanks for the info, Allen. Actually, this is a form that I'm speaking
of, not the actual table.

These fields have already been set, but I'm just trying to see if there
is a way to auto populate an expiration one-year from the date.

And these certifications always need to be redone exactly one year from
date entered.


Allen Browne wrote:
For the certificates that expire, will they *always* expire exactly 1
period
(year) after issued? Or could there be cases where you need to edit an
certificate so it is issued just for a week or 3 months, though they
normally expire a year later?

If it is always 1 period after issue, you must not store the expiry in
your
table. Instead, just calcuate the expiry when needed, and your
database
cannot be wrong.

If expiry could validly be different from the issue date plus one
period,
then you do need the Expires date in your table, and you would use the
AfterUpdate event of the issue date to set the expiry date.

More detail and examples in:
Calculated Fields
at:
http://allenbrowne.com/casu-14.html

(BTW, don't use Date as a field name. That's a reserved word in VBA
(for
the
system date), so the ambiguity will bite you. Perhaps IssueDate.)

Can someone tell me if this is possible? I have three fields:

I want the field [Expires] to update automatically by one year,
based
on the date of the date field [Date], but only if the second field's
value [CertCode] has a certain value. Let me explain:

I have certification dates which expire one year from date of
certification. But not all certification's have expirations. The
field
[CertCode] has a drop down box with a value of 1 thru various
numbers
up to 950. Codes 911 to 950 are non-expiring. So, I'd like to
leave
the [Expires] field blank when these values are chosen.

The way I have it set up now is:

Properties "All" tab and Event Procedure , On Click
[Expires] = DateAdd("yyyy", 1, [Date])

But all this formula does when you click the field is auto inputs a
one-year expiration, regardless of what is selected in the CertCode
field.