Help understanding DateAdd...

[Guest]

Thanks to all of you I was able to add the below expression to my query to
give me the date of the Friday of the week represented by the value in my
date field.

However, I would like to understand what's happening as opposed to just
pasting someone elses work.

The value of [Date] = 6/26/2007 as Tuesday
Result of Week Ending = 6/29/2007 a Friday

Expression:
Week Ending: DateAdd("w",6-Weekday([Date]),[Date])

I'm having a hard time understanding the concept of "w" = weekday and the
function Weekday.

I've been staring at this for so long, I may be rambling, but I hope someone
can help educate me.

 

[Carl Rapson]

Thanks to all of you I was able to add the below expression to my query to
give me the date of the Friday of the week represented by the value in my
date field.

However, I would like to understand what's happening as opposed to just
pasting someone elses work.

The value of [Date] = 6/26/2007 as Tuesday
Result of Week Ending = 6/29/2007 a Friday

Expression:
Week Ending: DateAdd("w",6-Weekday([Date]),[Date])

I'm having a hard time understanding the concept of "w" = weekday and the
function Weekday.

I've been staring at this for so long, I may be rambling, but I hope
someone
can help educate me.

Weekday([Date]) returns the day of the week as a number, 1-7 (1=Sunday, so
6=Friday). DateAdd then subtracts that value from 6(Friday) and adds that
number of days to the current date. So if today is Sunday, weekday=1 and
6-1=5 and 5+1=6, which gives us the upcoming Friday. Likewise, if today is
Saturday, weekday=7 and 6-7=-1 and -1+7=6, which again gives us Friday (the
day before).

HTH,

Carl Rapson

 

[Marshall Barton]

Thanks to all of you I was able to add the below expression to my query to
give me the date of the Friday of the week represented by the value in my
date field.

However, I would like to understand what's happening as opposed to just
pasting someone elses work.

The value of [Date] = 6/26/2007 as Tuesday
Result of Week Ending = 6/29/2007 a Friday

Expression:
Week Ending: DateAdd("w",6-Weekday([Date]),[Date])

I'm having a hard time understanding the concept of "w" = weekday and the
function Weekday.

I've been staring at this for so long, I may be rambling, but I hope someone
can help educate me.

Check VBA Help on DateAdd for details, but w is the code
letter for the day of the week (starting on Sunday) 1
through 7. The WeekDay function returns the day of the week
for its date argument do WeekDay(#6/26/2007#) is 3. 6-3 is
also 3 and adding 3 produces 6/29/2007, which is friday.
Personally, I think it would be a little clearer to use
DateAdd("d",6-Weekday([Date]),[Date])
but, in this case, the result is the same.

 

[John W. Vinson]

Thanks to all of you I was able to add the below expression to my query to
give me the date of the Friday of the week represented by the value in my
date field.

However, I would like to understand what's happening as opposed to just
pasting someone elses work.

The value of [Date] = 6/26/2007 as Tuesday
Result of Week Ending = 6/29/2007 a Friday

Expression:
Week Ending: DateAdd("w",6-Weekday([Date]),[Date])

I'm having a hard time understanding the concept of "w" = weekday and the
function Weekday.

I've been staring at this for so long, I may be rambling, but I hope someone
can help educate me.

I'd have used "d" rather than "w"... but either will in fact work. "d" adds
days. I've never quite figured out the rationale for "w", it certainly does
not add "weekdays" in the sense of skipping weekends!

The logic is that Weekday([Date]) returns 1 for a Sunday, 2 for Monday, ..., 7
for Saturday. So 6 - Weekday([date]) returns five days after the date if Date
happens to fall on a Sunday - that is, the next Saturday; one day prior to
[Date] if [Date] falls on Saturday, etc.

John W. Vinson [MVP]

 

[Guest]

Thanks to you all. I think I will just ignore "w" as my head wants to think
of it as a Weekday as opposed to a week day.

Thanks to all of you I was able to add the below expression to my query to
give me the date of the Friday of the week represented by the value in my
date field.

However, I would like to understand what's happening as opposed to just
pasting someone elses work.

The value of [Date] = 6/26/2007 as Tuesday
Result of Week Ending = 6/29/2007 a Friday

Expression:
Week Ending: DateAdd("w",6-Weekday([Date]),[Date])

I'm having a hard time understanding the concept of "w" = weekday and the
function Weekday.

I've been staring at this for so long, I may be rambling, but I hope someone
can help educate me.

I'd have used "d" rather than "w"... but either will in fact work. "d" adds
days. I've never quite figured out the rationale for "w", it certainly does
not add "weekdays" in the sense of skipping weekends!

The logic is that Weekday([Date]) returns 1 for a Sunday, 2 for Monday, ..., 7
for Saturday. So 6 - Weekday([date]) returns five days after the date if Date
happens to fall on a Sunday - that is, the next Saturday; one day prior to
[Date] if [Date] falls on Saturday, etc.

John W. Vinson [MVP]