A query on "This Week"

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
 
V

Van T. Dinh

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

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())
 
R

Robert Gillard

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

Bob
 
V

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