Increment years

[Jenn Civello]

I have a form that I need to have a field that increments by several
different numbers. I already have this formula working for 7 years, but I
need to add other years to the formula. Can you please let me know what I
need to change the formula to.
=DateSerial(Year([StartDate])+7,Month([StartDate]),Day([StartDate]))

Thanks
Jenn

 

[babyatx13 via AccessMonster.com]

I have a form that I need to have a field that increments by several
different numbers. I already have this formula working for 7 years, but I
need to add other years to the formula. Can you please let me know what I
need to change the formula to.
=DateSerial(Year([StartDate])+7,Month([StartDate]),Day([StartDate]))

Thanks
Jenn

Don't hard code #'s, instead of 7 use a table with the numbers you want to
use and look them up.
DLookup("Field", "Table", "Record #") in the place of "7"
=DateSerial(Year([StartDate])+DLookup("Field", "Table", "Record #") ,Month(
[StartDate]),Day([StartDate]))


K Board

 

[John W. Vinson]

I have a form that I need to have a field that increments by several
different numbers. I already have this formula working for 7 years, but I
need to add other years to the formula. Can you please let me know what I
need to change the formula to.
=DateSerial(Year([StartDate])+7,Month([StartDate]),Day([StartDate]))

Thanks
Jenn

I guess I don't understand your question. Where did the 7 come from?
Where do you WANT the number of years to come from? A form control? a
user prompt? A VBA variable?

John W. Vinson [MVP]

 

[Jenn Civello]

Does it matter if her have the "Record #" in the formula? We took it out,
and we are getting this error. "#Name?". Our formula is
=DateSerial(Year([StartDate])+DLookUp("# of Years to be
Archived","DestructionYear"),Month([StartDate]),Day([StartDate])). # of
years to be archived is the field and Destruction Year is the table. What
are we doing wrong?

I have a form that I need to have a field that increments by several
different numbers. I already have this formula working for 7 years, but I
need to add other years to the formula. Can you please let me know what I
need to change the formula to.
=DateSerial(Year([StartDate])+7,Month([StartDate]),Day([StartDate]))

Thanks
Jenn

Don't hard code #'s, instead of 7 use a table with the numbers you want to
use and look them up.
DLookup("Field", "Table", "Record #") in the place of "7"
=DateSerial(Year([StartDate])+DLookup("Field", "Table", "Record #")
,Month(
[StartDate]),Day([StartDate]))


K Board

 

[babyatx13 via AccessMonster.com]

Does it matter if her have the "Record #" in the formula? We took it out,
and we are getting this error. "#Name?". Our formula is
=DateSerial(Year([StartDate])+DLookUp("# of Years to be
Archived","DestructionYear"),Month([StartDate]),Day([StartDate])). # of
years to be archived is the field and Destruction Year is the table. What
are we doing wrong?
[quoted text clipped - 13 lines]

K Board

Your DLookup needs to refer to a table with years in it

Example:
tblYears
KYrID NoOfYears
AutoNumber Number

DLookup("Field", "Table", "Record #")
DLookup("NoOfYears", "tblYears", "KYrID")

If you would like you can have this table only hold one record and just
change the NoOfYears for each report, in which case your DLookup would look
like this.

DLookup("NoOfYears", "tblYears", "001â€)


K Board