Calculating dates dates in the future

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

Tony Williams

I have a form that has a control DateDestroytxt and a control DocTypetxt. I
want the DateDestroytxt date to be a number of months in the future which
are dependent upon the type of document in Doctypetxt eg if doctypetxt is a
letter the dateDestroytxt date should be 6 months after today. I know I can
use the DateAdd function but how do I add months and make it dependent on
the Doctypetxt control?
TIA
Tony
 
I have a form that has a control DateDestroytxt and a control DocTypetxt. I
want the DateDestroytxt date to be a number of months in the future which
are dependent upon the type of document in Doctypetxt eg if doctypetxt is a
letter the dateDestroytxt date should be 6 months after today. I know I can
use the DateAdd function but how do I add months and make it dependent on
the Doctypetxt control?
TIA
Tony
Click to expand...

Just reference the Doctypetxt control in your call to DateAdd:

DateAdd("m", Choose([Doctypetxt] = "Letter", 6, [Doctypetxt] = "Memo",
1, ...), Date())

You may want instead to create a Retention table with document types
and the retention period for that document type, or add a retention
period field to the table of document types. You could then simply
look up the retention using DLookUp.
 
Tony,
How many choices?
If just a few:
=IIf([DocTypext] = "letter",DateAdd("m",6,Date()),IIf([DocTypetxt] =
"What?",DateAdd("m",12,Date()),DateAdd("m",24,Date()))

6 months, 12 months, or 24 months will be added to the current date.

If you have many more choices, make a User Defined function in a module, and
refer to it in the control's control source:
=YourFunction([DocTypetxt])

Your Function can then utilize a more complex If..Then .. Else,
or a Select Case statement to return the future date to the control.
See Access help on how to write them.
 
Thanks John given me ideas
Tony
John Vinson said:
I have a form that has a control DateDestroytxt and a control DocTypetxt. I
want the DateDestroytxt date to be a number of months in the future which
are dependent upon the type of document in Doctypetxt eg if doctypetxt is a
letter the dateDestroytxt date should be 6 months after today. I know I can
use the DateAdd function but how do I add months and make it dependent on
the Doctypetxt control?
TIA
Tony
Click to expand...

Just reference the Doctypetxt control in your call to DateAdd:

DateAdd("m", Choose([Doctypetxt] = "Letter", 6, [Doctypetxt] = "Memo",
1, ...), Date())

You may want instead to create a Retention table with document types
and the retention period for that document type, or add a retention
period field to the table of document types. You could then simply
look up the retention using DLookUp.
Click to expand...
 
Thanks Fred the first option looks within my capabilities, I'd need more
guidance on the second although it looks better
Tony
Fredg said:
Tony,
How many choices?
If just a few:
=IIf([DocTypext] = "letter",DateAdd("m",6,Date()),IIf([DocTypetxt] =
"What?",DateAdd("m",12,Date()),DateAdd("m",24,Date()))

6 months, 12 months, or 24 months will be added to the current date.

If you have many more choices, make a User Defined function in a module, and
refer to it in the control's control source:
=YourFunction([DocTypetxt])

Your Function can then utilize a more complex If..Then .. Else,
or a Select Case statement to return the future date to the control.
See Access help on how to write them.
--
Fred

Please reply only to this newsgroup.
I do not reply to personal e-mail.


Tony Williams said:
I have a form that has a control DateDestroytxt and a control
Click to expand...
DocTypetxt.
I
want the DateDestroytxt date to be a number of months in the future which
are dependent upon the type of document in Doctypetxt eg if doctypetxt
Click to expand...
is
a
letter the dateDestroytxt date should be 6 months after today. I know I can
use the DateAdd function but how do I add months and make it dependent on
the Doctypetxt control?
TIA
Tony
Click to expand...
Click to expand...
 
Back
Top