Re: Convert dates to "week beginning.." dates

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" <jubu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BFD3A2C0-8E97-4C4E-B796-05A97959DB88@xxxxxxxxxxxxxxxx
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" <jubu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:359275D0-B8C3-4EC0-93F5-FBA60B2811A4@xxxxxxxxxxxxxxxx
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






.

Relevant Pages

  • Re: Need a list of variables in Tables
    ... If you don't like to code, you can use the documenter with instructions from ... Duane Hookom ... MS Access MVP ... I have an .mdb file with about 30 tables and each table has a dozen or ...
    (microsoft.public.access.modulesdaovba)
  • Re: vertical and horizontal lines in Subreport
    ... Duane Hookom ... MS Access MVP ... instructions - But I see your point - it makes sense. ... not when I preview the master and subreport together. ...
    (microsoft.public.access.reports)
  • Re: Convert dates to "week beginning.." dates
    ... MS Access MVP ... Per your instructions I ... "Duane Hookom" wrote: ...
    (microsoft.public.access.queries)
  • Re: Change Row Color if Field Value=Yes
    ... Users should view/edit records in forms, ... >> Duane Hookom ... >> MS Access MVP ... Would you be able to give me specific instructions ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Beginner query help please
    ... MS Access MVP ... I've noticed that if you select all the query output (in this case ... Duane Hookom wrote: ... Controls in most query solutions would not be ignored. ...
    (microsoft.public.access.queries)
Loading