How do I refer the Friday of a particular week?

O

odudley

I have data entered in a field and I need to create a field in a query that
calls on that date and returns the date of the Friday of that week. For
instance if "Entry Date" is my date field and "5/4/2005" or "5/2/2005"is in
this field what formula would I use to return "5/6/2005"?
 
K

Ken Snell [MVP]

Perhaps this function will help:

Public Function DateOfSpecificWeekDay(ByVal OriginalDate As Date, _
ByVal intWeekDay As Integer) As Date
' Ken Snell 29 December 2004
' ** THIS FUNCTION RETURNS THE DATE OF THE SPECIFIC DAY OF THE WEEK
' ** IN WHICH THE ORIGINAL DATE IS.
' ** intWeekDay = 1 is Sunday, 2 is Monday, etc.

On Error Resume Next

DateOfSpecificWeekDay = DateAdd("d", -DatePart("w", OriginalDate, _
1) + intWeekDay, OriginalDate)
Err.Clear
End Function
 
O

odudley

Let me rephrase: I have an entry date and this data is entered everyday. I'd
like a field in my query that calculates the Friday of that week using the
entry date.
 
K

Ken Snell [MVP]

See the function that I posted. In your query, create a calculated field
with an expression that calls the function and uses your other field with a
date value as one of the arguments that you pass to the function:

TheFridayDate: DateOfSpecificWeekDay([DateFieldName], 6)
 
O

Ofer

I ment add that to the query

select a,b,c,Mydate, dateadd("d",6-day(Mydate),Mydate) as FridayOfTheWeek
From Table
 
O

odudley

The point of the formula is to automatically change the date, no matter what
the date, to the Friday of that week. Your posting seems to increment my
dates. Changing the scripting every week would defeat the purpose of the
code. I am not only refering to one week I am refering to this week and every
week proceeding. I'm trying to incorporate the Weekday function in Access but
it doesn't seem to work. I'm thinking I may have the syntax wrong.

Ken Snell said:
See the function that I posted. In your query, create a calculated field
with an expression that calls the function and uses your other field with a
date value as one of the arguments that you pass to the function:

TheFridayDate: DateOfSpecificWeekDay([DateFieldName], 6)

--

Ken Snell
<MS ACCESS MVP>

odudley said:
Let me rephrase: I have an entry date and this data is entered everyday.
I'd
like a field in my query that calculates the Friday of that week using the
entry date.
Click to expand...
Click to expand...
 
O

odudley

Thank you very much. I changed around a couple of things to make the code
work for what I wanted:
ProcessDate: DateAdd("w",6-Weekday([Update Emp]),[Update Emp])
This returns the Friday of the week of [Update Emp]. My only problem now is
that an error is returned in the ProcessDate field for any blank [Update Emp]
field
 
O

Ofer

if you can't give it a default value then use the iif to check first if its
null

iif(isnull([Update Emp],"",DateAdd("w",6-Weekday([Update Emp]),[Update Emp]))


odudley said:
Thank you very much. I changed around a couple of things to make the code
work for what I wanted:
ProcessDate: DateAdd("w",6-Weekday([Update Emp]),[Update Emp])
This returns the Friday of the week of [Update Emp]. My only problem now is
that an error is returned in the ProcessDate field for any blank [Update Emp]
field

Ofer said:
I ment add that to the query

select a,b,c,Mydate, dateadd("d",6-day(Mydate),Mydate) as FridayOfTheWeek
From Table
Click to expand...
Click to expand...
 
Top