How do I refer the Friday of a particular week?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

odudley - 04 May 2005 20:36 GMT

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"?

Reply to this Message

Ofer - 04 May 2005 20:51 GMT

You can try that

dateadd("d",6-day(Mydate),Mydate)

> 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"?

Reply to this Message

Ken Snell [MVP] - 04 May 2005 22:33 GMT

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

Signature

Ken Snell
<MS ACCESS MVP>

>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"?

Reply to this Message

odudley - 05 May 2005 09:00 GMT

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.

Reply to this Message

Ken Snell [MVP] - 05 May 2005 13:36 GMT

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)

Signature

Ken Snell
<MS ACCESS MVP>

> 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.

Reply to this Message

odudley - 05 May 2005 20:11 GMT

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.

> 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
[quoted text clipped - 6 lines]
> > like a field in my query that calculates the Friday of that week using the
> > entry date.

Reply to this Message

Ofer - 05 May 2005 18:21 GMT

I ment add that to the query

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

> 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.

Reply to this Message

odudley - 05 May 2005 20:24 GMT

Your code returns the 6th day of the month according to my referenced date

> I ment add that to the query
>
[quoted text clipped - 4 lines]
> > like a field in my query that calculates the Friday of that week using the
> > entry date.

Reply to this Message

odudley - 05 May 2005 20:36 GMT

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

> I ment add that to the query
>
[quoted text clipped - 4 lines]
> > like a field in my query that calculates the Friday of that week using the
> > entry date.

Reply to this Message

Ofer - 05 May 2005 22:12 GMT

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]))

> Thank you very much. I changed around a couple of things to make the code
> work for what I wanted:
[quoted text clipped - 11 lines]
> > > like a field in my query that calculates the Friday of that week using the
> > > entry date.

Reply to this Message