Day of the Week in Query

[Kosmo2]

Can anybody help me with this please :-

in a query how do I take a specific date and return another date which is always on the Friday of the same week ?

 

[rac2]

So you have a date in a column. Call it theDate.
And you want to know the date of the next Friday.

SELECT DateAdd( "d", IIF( (WeekDay( theDate)>6),6,(6-WeekDay( theDate)) ),  theDate ) AS Express
FROM
aTable

What day of the week is it? WeekDay( theDate), Sun=1...Sat=7.
How many days until Friday? (6-WeekDay( theDate))
except on Saturday, day 7, it is 6 days until Friday, not -1 days.

Add that many days to theDate.

It ain't pretty but I think it will work.

 

[r937]

define "same week"

if i take a specific date like september 14, 2002, which friday would you like to it return? the friday of the same week is the 13th, right?

rudy

http://rudy.ca/

 

[BattleDroid42]

Try this function:

Function LastBusDay(D As Variant) As Variant
'
' Returns the date of the last business day (Mon-Fri) in a month
'
Dim D2 As Variant
If VarType(D) <> 7 Then
LastBusDay = Null
Else
D2 = DateSerial(Year(D), Month(D) + 1, 0)
Do While WeekDay(D2) = 1 Or WeekDay(D2) = 7
D2 = D2 - 1
Loop
LastBusDay = D2
End If
End Function

 

[MichaelRed]

It can be simplified if you just want the &quot;closest&quot; week day (Friday).

Public Function basCurWkDayDt(DtIn As Date, WkDay As Integer) As Date

    'Michael Red    Tek-Tips thread703-360132   9/16/02

    Dim tmpDate As Date
    Dim Offset As Integer
    'Example Usage
    '? basCurWkDayDt(Date, vbMonday)
    '9/16/02

    '? basCurWkDayDt(#8/21/2002#, vbMonday)
    '8/19/02

    '? basCurWkDayDt(Date, vbFriday)
    '9/20/02


    'Guess the date (coluld be wrong week)
    tmpDate = DateAdd("d", 8 - Weekday(DtIn, WkDay), DtIn)
    Offset = DatePart("ww", DtIn) - DatePart("ww", tmpDate)

    basCurWkDayDt = DateAdd("ww", Offset, tmpDate)
    

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over