Convert dates to "week beginning.." dates

I am using Access 2003 and have a query of daily entry information including
a Date field. I would like to be able to create another column that shows
the "Week Beginning..." for each of the dates. Example: 7/1/06 would show
the week beginning date as 6/25/06; 7/5/06 would show the week beginning date
as 7/2/06; etc.

I tried the DatePart function which converted the date to a serial number
representing the number of the week within the year, and thought of using a
table which correlates the number to a "week beginning date". However, that
doesn't work well if my query includes dates carried over to a different year.

Any suggestions would be MUCH appreciated!!!
--
jubu

Use DateAdd() to subtract the weekday of the datefield from the datefield
and then add 1 back in to get Sunday.

--
Duane Hookom
MS Access MVP

"jubu" wrote in message
...
I am using Access 2003 and have a query of daily entry information
including
a Date field. I would like to be able to create another column that shows
the "Week Beginning..." for each of the dates. Example: 7/1/06 would
show
the week beginning date as 6/25/06; 7/5/06 would show the week beginning
date
as 7/2/06; etc.

I tried the DatePart function which converted the date to a serial number
representing the number of the week within the year, and thought of using
a
table which correlates the number to a "week beginning date". However,
that
doesn't work well if my query includes dates carried over to a different
year.

Any suggestions would be MUCH appreciated!!!
--
jubu

I looked up the info about DateAdd, but I don't understand how it will find
the beginning of the week. Per your instructions (as I understand them) I am
constructing the formula as follows:
=(DateAdd("w",-1,[Date]))+1
This is simply returning the same date; I really want to find the Sunday of
the week in which the date falls. Example:
6/22/06, I want to return the value 6/18/06
6/24/06, I want to return the value 6/18/06

Thanks in advance.
--
jubu


"Duane Hookom" wrote:

Use DateAdd() to subtract the weekday of the datefield from the datefield
and then add 1 back in to get Sunday.

--
Duane Hookom
MS Access MVP

"jubu" wrote in message
...
I am using Access 2003 and have a query of daily entry information
including
a Date field. I would like to be able to create another column that shows
the "Week Beginning..." for each of the dates. Example: 7/1/06 would
show
the week beginning date as 6/25/06; 7/5/06 would show the week beginning
date
as 7/2/06; etc.

I tried the DatePart function which converted the date to a serial number
representing the number of the week within the year, and thought of using
a
table which correlates the number to a "week beginning date". However,
that
doesn't work well if my query includes dates carried over to a different
year.

Any suggestions would be MUCH appreciated!!!
--
jubu

You want to subtract the number of days "d" calculated from the WeekDay()
function.
=DateAdd("d",-WeekDay([Date]) +1, [date])

--
Duane Hookom
MS Access MVP

"jubu" wrote in message
...
I looked up the info about DateAdd, but I don't understand how it will find
the beginning of the week. Per your instructions (as I understand them) I
am
constructing the formula as follows:
=(DateAdd("w",-1,[Date]))+1
This is simply returning the same date; I really want to find the Sunday
of
the week in which the date falls. Example:
6/22/06, I want to return the value 6/18/06
6/24/06, I want to return the value 6/18/06

Thanks in advance.
--
jubu


"Duane Hookom" wrote:

Use DateAdd() to subtract the weekday of the datefield from the datefield
and then add 1 back in to get Sunday.

--
Duane Hookom
MS Access MVP

"jubu" wrote in message
...
I am using Access 2003 and have a query of daily entry information
including
a Date field. I would like to be able to create another column that
shows
the "Week Beginning..." for each of the dates. Example: 7/1/06 would
show
the week beginning date as 6/25/06; 7/5/06 would show the week
beginning
date
as 7/2/06; etc.

I tried the DatePart function which converted the date to a serial
number
representing the number of the week within the year, and thought of
using
a
table which correlates the number to a "week beginning date". However,
that
doesn't work well if my query includes dates carried over to a
different
year.

Any suggestions would be MUCH appreciated!!!
--
jubu

It worked! Thank you so much for your help.
--
jubu


"Duane Hookom" wrote:

You want to subtract the number of days "d" calculated from the WeekDay()
function.
=DateAdd("d",-WeekDay([Date]) +1, [date])

--
Duane Hookom
MS Access MVP

"jubu" wrote in message
...
I looked up the info about DateAdd, but I don't understand how it will find
the beginning of the week. Per your instructions (as I understand them) I
am
constructing the formula as follows:
=(DateAdd("w",-1,[Date]))+1
This is simply returning the same date; I really want to find the Sunday
of
the week in which the date falls. Example:
6/22/06, I want to return the value 6/18/06
6/24/06, I want to return the value 6/18/06

Thanks in advance.
--
jubu


"Duane Hookom" wrote:

Use DateAdd() to subtract the weekday of the datefield from the datefield
and then add 1 back in to get Sunday.

--
Duane Hookom
MS Access MVP

"jubu" wrote in message
...
I am using Access 2003 and have a query of daily entry information
including
a Date field. I would like to be able to create another column that
shows
the "Week Beginning..." for each of the dates. Example: 7/1/06 would
show
the week beginning date as 6/25/06; 7/5/06 would show the week
beginning
date
as 7/2/06; etc.

I tried the DatePart function which converted the date to a serial
number
representing the number of the week within the year, and thought of
using
a
table which correlates the number to a "week beginning date". However,
that
doesn't work well if my query includes dates carried over to a
different
year.

Any suggestions would be MUCH appreciated!!!
--
jubu