A query on "This Week"

22 Feb 2005 7:31 PM

Robert Gillard

I need to run a query against a date field so I can see how many items have
been added this week. This needs to be a perminat query ie click a button
and it will run without the need to change or incert dates etc.
I already have a query that will check for any items this month

>=DateSerial(Year(Date()),Month(Date())+0,+1)

what I am trying to find is one that will do the same job for "this week"
(and if possible a second for "last week" as they are bound to want to
compare)

Any thought greatfully received.

Bob

22 Feb 2005 9:11 PM

Van T. Dinh

You need to specify your week. Is this Monday to Sunday (inclusive) or
Sunday to Saturday?

--
HTH
Van T. Dinh
MVP (Access)

Show quote

"Robert Gillard" <b**@mystical.demon.co.uk> wrote in message
news:sMLSd.121766$68.22468@fe1.news.blueyonder.co.uk...
> I need to run a query against a date field so I can see how many items
have
> been added this week. This needs to be a perminat query ie click a button
> and it will run without the need to change or incert dates etc.
> I already have a query that will check for any items this month
> >=DateSerial(Year(Date()),Month(Date())+0,+1)
>
> what I am trying to find is one that will do the same job for "this week"
> (and if possible a second for "last week" as they are bound to want to
> compare)
>
> Any thought greatfully received.
>
> Bob
>
>

22 Feb 2005 9:49 PM

Robert Gillard

As a company we do not work on a Saturday or Sunday, so which ever is
easiest.

Bob

Show quote

"Van T. Dinh" <VanThien.D***@discussions.microsoft.com> wrote in message
news:OiFsWJSGFHA.3376@TK2MSFTNGP12.phx.gbl...
> You need to specify your week. Is this Monday to Sunday (inclusive) or
> Sunday to Saturday?
>
> --
> HTH
> Van T. Dinh
> MVP (Access)
>
>
>
> "Robert Gillard" <b**@mystical.demon.co.uk> wrote in message
> news:sMLSd.121766$68.22468@fe1.news.blueyonder.co.uk...
> > I need to run a query against a date field so I can see how many items
> have
> > been added this week. This needs to be a perminat query ie click a
button
> > and it will run without the need to change or incert dates etc.
> > I already have a query that will check for any items this month
> > >=DateSerial(Year(Date()),Month(Date())+0,+1)
> >
> > what I am trying to find is one that will do the same job for "this
week"
> > (and if possible a second for "last week" as they are bound to want to
> > compare)
> >
> > Any thought greatfully received.
> >
> > Bob
> >
> >
>
>

23 Feb 2005 3:10 AM

Van T. Dinh

One way is as PCDatasheet advised.

However, I prefer to use the BETWEEN [StartDate] AND [EndDate] which is much
more effcient, especially if the DateField is indexed.

Since DateAdd("d", -Weekday(Date()) +2, Date()) gives Monday's date and
DateAdd("d", -Weekday(Date()) +6, Date()) gives Friday's date, you can use
the criteria:

.... WHERE [YourDateField]
BETWEEN DateAdd("d", -Weekday(Date()) +2, Date())
AND DateAdd("d", -Weekday(Date()) +6, Date())

for the current week (you can use similar expression for last week).

This assume you don't have non-zero time component in [YourDateField].

You need to test this since I think Weekday function may behave differently
depending on your Regional Settings.

--
HTH
Van T. Dinh
MVP (Access)

Show quote

"Robert Gillard" <b**@mystical.demon.co.uk> wrote in message
news:_NNSd.196760$K7.41198@fe2.news.blueyonder.co.uk...
> As a company we do not work on a Saturday or Sunday, so which ever is
> easiest.
>
> Bob
>
>

22 Feb 2005 9:13 PM

PC Datasheet

Rather than your date field, put the following expression in a blank field
in your query:
WeekOfItems:DatePart("ww",[NameOfYourDateField])
To get the items added this week, put the following expression in the
criteria:
DatePart("ww",[Date())

--
                                        PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
                              resou***@pcdatasheet.com
                                 www.pcdatasheet.com

Show quote

"Robert Gillard" <b**@mystical.demon.co.uk> wrote in message
news:sMLSd.121766$68.22468@fe1.news.blueyonder.co.uk...
> I need to run a query against a date field so I can see how many items
have
> been added this week. This needs to be a perminat query ie click a button
> and it will run without the need to change or incert dates etc.
> I already have a query that will check for any items this month
> >=DateSerial(Year(Date()),Month(Date())+0,+1)
>
> what I am trying to find is one that will do the same job for "this week"
> (and if possible a second for "last week" as they are bound to want to
> compare)
>
> Any thought greatfully received.
>
> Bob
>
>