Query Expression

[bluke]

Could someone provide me with an expression that would always return data from last week? I've been playing around with serialdate and datepart but can't quite figure it out.

Appreciate the help!

B.

 

[Brianwarnock]

Do you mean 7 days ago, or all of last week?

Brian

 

[raskew]

Hi -

Place this in the criteria cell of your date field (assuming it's in date/time data format). Your query will then return only those records between Sunday of the preceding week and Saturday of the preceding week. Should work regardless of whether the field includes time.

>= date()-weekday(date())-6 AND <date()-weekday(date())+1

HTH - Bob

 

[bluke]

Thanks for this but how would I modfiy this if I need to return all data for the full week Monday through Sunday?

 

[Brianwarnock]

The fact that you asked that question means that you did not analyse Bob's answer so that you understood it, it is no use just copying solutions you must understand them to progress.

Brian

 

[Brianwarnock]

Feel I might have been a bit harsh there, having a bad day with another poster.
Ok Bob's formulae works as workday returns 1-7 for Sunday to Saturday once you step outside this comfort zone a simple formulae wont work, infact a function is the only way I can think of, which is why I asked for clarification of requirements earlier.
For Blukes needs, Mon-Sun, Sundays value must be 8 and the fixed parts -5 and +2.
If you need help with the function come back.

Brian

 

[raskew]

>=date()-weekday(date())-5 AND <date()-weekday(date())+2

Brian - Must be getting dense(r). You lost me on: Sundays value must be 8

Bob

 

[Brianwarnock]

With the formula just quoted if we take say Mon 14th Jan he requires Mon7th to Sun 13


>=date()-weekday(date())-5 AND <date()-weekday(date())+2
gives 14-2-5 and 14-2+2 = 7th to 14 which is correct but if its Sunday 13th
he requires 31st Dec to 5th Jan but gets
gives13-1-5 to 13-1+2 = 7th to 14th
if Sunady is 8
13-8-5=0 ie 31st Dec 13-8+2 =7th which is what the formula requires.

Brian

 

[raskew]

Brian -

Good point! One I'd overlooked entirely.

Designating Sunday as 8 was what baffled me. Although a tad cumbersome, the following criteria would seem to work:

>=date()-weekday(date())-iif(weekday(date())=1,7,0)-5 AND <date()-weekday(date())-iif(weekday(date())=1,7,0)+2

The two parts (>= and <) work out like this (from the debug window)

x = #1/13/08#
' the >= portion
? x-weekday(x)-iif(weekday(x)=1,7,0)-5
12/31/2007 
' the < portion
? x-weekday(x)-iif(weekday(x)=1,7,0)+2
1/7/2008

Best Wishes - Bob

 

[Brianwarnock]

I had wondered about putting an IIf in the formulae but would have elected for a function as I would probably have messed up the formula.

Brian