Calculate Date

[Anthony W]

I have a field on my form that when clicked makes a text
box visible so that you can enter a number of weeks into
it and it will then calculate that number of weeks on top
of another field to give me a date. Is their anyway of
doing this so that after calculation, it always lands on
the nearest Friday.


Thanks in Advance


Anthony Webb

 

[John Vinson]

I have a field on my form that when clicked makes a text
box visible so that you can enter a number of weeks into
it and it will then calculate that number of weeks on top
of another field to give me a date. Is their anyway of
doing this so that after calculation, it always lands on
the nearest Friday.

Nearest? or next?

Try the following in the AfterUpdate of the weeks control. I'm using
txtStartdate as the name of the control containing the base date,
txtWeeks as the name of the textbox containing an integer number of
weeks, and txtTarget as the name of the textbox in which to store the
Friday, and storing the next Friday after the selected date:

Private Sub txtWeeks_AfterUpdate()
If Me!txtWeeks & "" = "" Then Exit Sub ' user blanked it out
If IsDate(Me!txtStartdate) Then
Me!txtTarget = DateAdd("ww", Me!txtWeeks, CDate(Me!txtStartdate) _
+ DateAdd("d", 7 - DatePart("w", Me!txtStartdate, vbFriday), _
Me!txtStartdate)
Else
Msgbox "Please fill in Start Date", vbOKOnly
End If
End Sub