Week ending Friday

  • Thread starter Thread starter Sandy
  • Start date Start date
S

Sandy

I have a table with a date field. What I would like to do
is create a new field titled "Week Ending" where it
displays a date or number (ex. "ww")as that Friday. For
example Thursday, 1/1/04 would be displayed as 1/2/04 and
Saturday 1/3/04 would be displayed as 1/9/04. The "ww"
query doesn't work because it displays weeks as Sunday to
Saturday.

Thanks in advance for your help!
 
Sandy
Let's say the name of your date field is "ddate". The following will work as a column entry in a query and yield the corresponding Friday for that week.
CorresFri: DateValue(Format([ddate]+6-Weekday([ddate]),"mm") & "/" & Format([ddate]+6-Weekday([ddate]),"dd") & "/" & Format([ddate]+6-Weekday([ddate]),"yyyy")
-BigMan
----- Sandy wrote: ----

I have a table with a date field. What I would like to do
is create a new field titled "Week Ending" where it
displays a date or number (ex. "ww")as that Friday. For
example Thursday, 1/1/04 would be displayed as 1/2/04 and
Saturday 1/3/04 would be displayed as 1/9/04. The "ww"
query doesn't work because it displays weeks as Sunday to
Saturday

Thanks in advance for your help
 
Try using the following NOT FULLY TESTED statement

DateAdd("d",7-Weekday(SomeDate,7),SomeDate)
 
Thank you so much, they both worked!
-----Original Message-----
Sandy,
Let's say the name of your date field
Click to expand...
is "ddate". The following will work as a column entry in
a query and yield the corresponding Friday for that week.:
CorresFri: DateValue(Format([ddate]+6-Weekday
Click to expand...
([ddate]),"mm") & "/" & Format([ddate]+6-Weekday
([ddate]),"dd") & "/" & Format([ddate]+6-Weekday
([ddate]),"yyyy"))
 
Back
Top