Display a date

[Gert]

Hi,

I need to display Friday's date for any given week number in a
parameter query.
The parameter will be the week number.

Thanx!

 

[Graham R Seach]

Gert,

Add the following to a standard module:
Public Function GetFridayOfWeek(iWeekNo As Integer) As Date
Dim tmpDate As Date

tmpDate = DateAdd("ww", iWeekNo, DateSerial(Year(Date), 1, 1))
GetFridayOfWeek = tmpDate - (Weekday(tmpDate, vbFriday) - 1)
End Function

Then call it in your SQL, like so:
PARAMETERS WeekNo Short;
SELECT [somefield], GetFridayOfWeek([WeekNo]) As FridayDate
FROM tblMyTable;

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia