calculate a specific day of the week

I need to calculate from the entry date of a youth the specific date of the
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/10387-calculate-a-specific-day-of-week.html
Tuesday within a 30 day limit.

The youth in our program must see the doctor within 30 days, however, the
doctor is only in the office on Tuesdays. I want to calculate the nearest
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10387
Tuesday to the 30 day mark.

Thanks,

Dendalee

*

 
Do share your immediate thoughts or reactions on this issue? We value your views! Login Now! or Sign Up Today! to share your views with us.. Gurfateh!

Public Function Tuesday30(datDate As Date) As Date
On Error GoTo Err_Label
Dim datThirtyDays As Date
datThirtyDays = DateAdd("dd", 30, datDate)
Do While Weekday(datThirtyDays, vbSunday) <> 3
datThirtyDays = DateAdd("d", -1, datThirtyDays)
Loop
Tuesday30 = datThirtyDays

Exit_Label:
Exit Function
Err_Label:
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10387
MsgBox Err.Description
Resume Exit_Label
End Function

"Dendalee" wrote in message
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10387
news:05343D88-8FD9-407B-8DFB-BD0BC32F8471@microsoft.com...
>I need to calculate from the entry date of a youth the specific date of the
> Tuesday within a 30 day limit.
>
> The youth in our program must see the doctor within 30 days, however, the
> doctor is only in the office on Tuesdays. I want to calculate the nearest
> Tuesday to the 30 day mark.
>
> Thanks,
>
> Dendalee

Dendalee,

this function returns the nearest Tuesday in 30 days.

=iif(weekday([fecha1]+30;3)<5;
[fecha1]+30+(1-weekday([fecha1]+30;3));
[fecha1]+30+(8-weekday([fecha1]+30;3)))
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10387

were Fecha1 is the date to calculate from.

or you may put this function in a module

Function Tuesday30(DateFrom As Date) As Date
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10387
Tuesday30 = DateFrom + 30
If Weekday(Tuesday30, 3) < 5 Then
Tuesday30 = Tuesday30 + (1 - Weekday(Tuesday30, 3))
Else
Tuesday30 = Tuesday30 + (8 - Weekday(Tuesday30, 4))
End If
End Function

and call as Tuesday30(Fecha1) from a query or a form.

Regards,

Valentín Playá
Sonotronic S.A.
Madrid, Spain