A query on "This Week"

  • Thread starter Thread starter Robert Gillard
  • Start date Start date
R

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)
Click to expand...

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
 
You need to specify your week. Is this Monday to Sunday (inclusive) or
Sunday to Saturday?
 
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())
 
As a company we do not work on a Saturday or Sunday, so which ever is
easiest.

Bob
 
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.
 
Back
Top