Week ending Friday

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!
 
B

BigManT

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
 
J

John Spencer (MVP)

Try using the following NOT FULLY TESTED statement

DateAdd("d",7-Weekday(SomeDate,7),SomeDate)
 
S

Sandy

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"))
 
Top