How do you write "this week"

[Robert Gillard]

I have a date field (shown as short date) which I need to query for all
entries "this week". Could anybody let me know how I need to write that
please.

With thanks

Bob

 

[Jos van der Heijden via AccessMonster.com]

the syntax is datepart("ww",date())

 

[John Webb via AccessMonster.com]

Rob,

One way of doing as you suggest would be to add an extra column to your
query, and in this column writing an expression to calculate the week
number, as below:

DatePart("ww",[Start_Date])

where [Start_Date] would be the date field you wish to evaluate.

In the criteria for that column, you would need to place the following:

DatePart("ww",Now())

However, this will display all records that match that week number,
regardless of the year in question - therefore you would need to add a
further column to remove dates from a previous / future year, the
expression would look like:

Year([Start_Date])

and the criteria would be

Year(Now())

You could combine the two if you wanted, for example

expression = DatePart("ww",[Start_Date]) & ":" & year([Start_Date])
criteria = DatePart("ww",Now()) & ":" & year(now())


I'm sure there may be tidier ways of doing this, but this method would work
nonetheless.

Hope that helps

John

 

[John Vinson]

I have a date field (shown as short date) which I need to query for all
entries "this week". Could anybody let me know how I need to write that
please.

With thanks

Bob

If the week starts on Sunday and runs through Saturday, you can use a
date range criterion: on the Criteria line under the date field put

= DateAdd("d", 1 - Weekday(Date()), Date()) AND < DateAdd("d", 8 - Weekday(Date()), Date())

John W. Vinson[MVP]