Calculating dates dates in the future

[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

 

[John Vinson]

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

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.

 

[Fredg]

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.

 

[Tony Williams]

Thanks John given me ideas
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

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

Thanks Fred the first option looks within my capabilities, I'd need more
guidance on the second although it looks better
Tony

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.

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