turning a week # into a date with a specific and constant day

[cochise]

In a query, I group a range of dates into a week number with the following formula:

WeekNum: DatePart("ww",[Date],4)

How can I take that WeekNum and turn it into a date that is always a Tuesday with a label EndDate.

For Example, if:
WeekNum = 1
EndDate = 1/2/03 (tuesday)

or

WeekNum = 2
EndDate = 1/9/03 (tuesday)

Thanks for any help.

 

[Lightning]

Check out the Weekday Name function - Access On-line Help topic is WeekdayName Function

HTH
Lightning

 

[cochise]

Actually, I want the function to return a date (##/##/##), which I want to always be a Tuesday. Not return a string day. I'm trying to convert a week number into a tuesday date. I do have access to the year which relates to the week number.

 

[MichaelRed]

I guess I'm just a sucker for the whine and complain option. You Really SHOULD at least TRY the help system!

Public Function basDow(DtIn As Date, Optional DOW As Integer = vbSunday) As Date

    'Michael Red    11/9/02     'First Sunday

    'Sample usage
    '? basDOW(#11/10/02#)
    '11/10/02

    '? basDOW(Date, vbMonday)
    '11/4/02

    '? basDow(Date(), vbTuesday)
    '1/14/03

    Dim CurDay As Date

    'First, check it is already the Day of the Week desired
    If (Weekday(DtIn) = DOW) Then
        CurDay = DtIn
     Else
        CurDay = DateAdd("d", DOW - Weekday(DtIn), DtIn)
    End If

    basDow = CurDay

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[raskew]

You'll need to check that you're using the correct year's calendar
since the example weekdays are incorrect.

The following code will return the date of the nth occurrence of
a weekday following the 1st of Jan of the specified year.

Function GetNthDay(pDay As Integer, pIter As Integer, pYear As String) As Date
'*******************************************
'Name:      GetNthDay (Function)
'Purpose:   Return the date of the nth
'                 specified weekday in a year
'Parameters:
'   pDay  =  Integer between vbSunday (1) and vbSaturday (7)
'   pIter =    Integer representing the iteration, first, third, etc
'   pYear = Target year expressed in "yyyy" format
'Inputs: To return the 5th Tuesday in 2003, from the debug window:
'        ? getNthDay(3, 5, &quot;2003&quot;) <enter>
'Output:  2/4/03
'*******************************************

Dim dteDate As Date

'express Jan 1 of pYear as a date
dteDate = DateValue(&quot;1/1/&quot; & pYear)

'find first occurence of the specified weekday >= Jan 1st of
'the target year then add 7 * (the desired iteration -1) days
GetNthDay = DateAdd(&quot;d&quot;, 7 * (pIter - 1), dteDate - WeekDay(dteDate) _
+ pDay + IIf(WeekDay(dteDate) > pDay, 7, 0))

End Function

Note that the date returned may or may not correspond to the
week of the year unless the vbFirstFullWeek option is used in
the DatePart() function, e.g.

? datepart("ww", #2/4/03#,,vbFirstFullWeek)
5

 

[PaulBricker]

I'm not sure why you have to use the WeekNumber when you have the date but if you don't, you could use this.

EndDate[Date]-Weekday([Date]))+3

Noting what Bob said about your example dates not corresponding with the current calendar, this will give you the Tuesday of the week that corresponds to the value of the [Date] field.

Paul

 

[raskew]

Paul-
I'm also confused about where this is going and why we'd even want to do it in the first place.

I find the 'week' calculation very iffy. Played around with DatePart"ww".... and found that I could come up with a variety of results, depending on how one defines the first week of the year. Think it's a very shaky, unreliable parameter which I'd never willingly use in an application

Cochise--Can you tell us why it's important to go this route, and could we persuade you to go a different path?

Bob