Increment years

  • Thread starter Thread starter Jenn Civello
  • Start date Start date
J

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
 
Jenn said:
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
Click to expand...

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
 
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
Click to expand...

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]
 
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?


babyatx13 via AccessMonster.com said:
Jenn said:
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
Click to expand...

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
Click to expand...
 
Jenn said:
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
Click to expand...
Click to expand...

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
 
Back
Top