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

On Wed, 1 Oct 2003 14:09:21 +0000 (UTC), "Tony Williams" <(E-Mail Removed)>
wrote:

>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.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

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" <(E-Mail Removed)> wrote in message
news:blen6h$seo$(E-Mail Removed)...
> 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
>
>
>

Thanks John given me ideas
Tony
"John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news(E-Mail Removed)...
> On Wed, 1 Oct 2003 14:09:21 +0000 (UTC), "Tony Williams" <(E-Mail Removed)>
> wrote:
>
> >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.
>
> John W. Vinson[MVP]
> Come for live chats every Tuesday and Thursday
> http://go.compuserve.com/msdevapps?loc=us&access=public

Thanks Fred the first option looks within my capabilities, I'd need more
guidance on the second although it looks better
Tony
"Fredg" <(E-Mail Removed)> wrote in message
news:0CEeb.166015$(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:blen6h$seo$(E-Mail Removed)...
> > 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
> >
> >
> >
>
>