Calculate a date (from start date) when check Box is activated

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Oliver - 17 May 2008 13:21 GMT

Hi,
I have the follwing table
1. StartDate ( date Format)
2. Inc1Mo (checkBox)
3. SampleReadyOn (date Format)
I would like to program the following into a Form:
When the CheckBox is activated ( to yes) than:
1 month is add to the startDate and the new date is given into the
SampleReadyOn column.

For eg. if the Start date is 051708
When CheckBox is activated to " YES"
Than the following date shoud appears into SampleReadyOn = 061708 (Start
date + 1 month)
And I would like to have the same possibility for 3,6,12,24 months

Thanks for your help
Oliver

Reply to this Message

Al Campagna - 17 May 2008 14:40 GMT

Oliver,
Use the DateAdd function in the AfterUpdate event of Inc1Mo.
Private Sub Inc1Mo_AfterUpdate()
If Inc1Mo = True Then
SampleReadyOn = DateAdd("m", 1, StartDate)
End if
End Sub

Signature

hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

> Hi,
> I have the follwing table
[quoted text clipped - 14 lines]
> Thanks for your help
> Oliver

Reply to this Message

Linq Adams - 17 May 2008 14:43 GMT

Private Sub Inc1Mo_AfterUpdate()
If Inc1Mo = -1 Then
Me.SampleReadyOn = DateAdd("m", 1, Me.StartDate)
End If
End Sub

To do it for 3, 6 etcmonths, change the 1 to the appropriate number in the
DateAdd() function.

You should note, however, that

051708

is NOT A DATE, and Access won't recognize it as one! You have to have
standard delimiters between the date components, i.e.

05/17/08
or

05-17-08

or

05.17.08

before Access will allow it in a field defined as DateTime datatype and hence
nefore DateAdd() will work

Signature

There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Reply to this Message

Linq Adams - 17 May 2008 14:47 GMT

Sorry, should have added that if you want to be able to change you mind and
uncheck the box and delete the value you've placed in the SampleReadyOn field:

Private Sub Inc1Mo_AfterUpdate()
If Inc1Mo = -1 Then
Me.SampleReadyOn = DateAdd("m", 1, Me.StartDate)
Else
Me.SampleReadyOn = ""
End If
End Sub

Signature

There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Reply to this Message

Oliver - 18 May 2008 13:42 GMT

Dear Linq,
Thanks a lot for your help.
It works fine.
Warm Regards.
Oliver

> Sorry, should have added that if you want to be able to change you mind and
> uncheck the box and delete the value you've placed in the SampleReadyOn field:
[quoted text clipped - 6 lines]
> End If
> End Sub

Reply to this Message