Help for query thatl find events that took place LAST two weeks

[tony30]

Please help me with this query:
I have this expression from Microsoft help :



Year([SalesDate])* 53 + DatePart("ww", [SalesDate]) = Year(Date())*53 + DatePart("ww", Date()) - 1

which Returns records of transactions that took place during the last week. A week starts on Sunday and ends on Saturday.
It works fine, I tried to modify the expression for the "last two weeks", using -2 thinking that will do the trick, it does not work. Can someone help me modifying this expression? why does not work? any suggestions?
Thank you so much
PD: My table is simple, listing events that take place in the past, field is CheckinDate, table is TblBookings
Tony30

[Robeen]

I attached a screenshot using your expression example & it works for me.
In my query the Date field is named 'InspectionDate' - you'll have to change that.
the other fields in my query are off to the left so they are not showing.

See if you can use this logic in your query to make it work. Let us know if you have problems with it.

[tony30]

I just forget to include the operator<.
It works fine now

[tony30]

Dear Robeen:\
I am sorry to report that it does not work, I have the rsults attached there are a few that are the outside interval "last two weeks"' interval should be between 9/23/2012 and 10/6/12 and
I have quite a few outside the range.
Any thoughts?
Thanks
I thought it was the operator but it is something else taht is not working...
Thank you again..

[Robeen]

I misunderstood your question. Sorry. You are interested in data that falls in the previous two week Numbers - right?

Quite often - when I need to run reports/queries for a particular two-week period, I schedule them to run first thing in the morning [before I get to work] before any new transactions have been entered for the next two-week period. That is the logic I was using above - sorry.
In that case, the above logic would work.

However, with the '>', I believe it will give you everything that has a date 'greater than' the cutoff date.

I will see if I can figure out how to isolate the previous two week numbers and retrieve data for that range.

[RayMilhon]

Try this:

In the Criteria line use between (Year(Date())*53 + DatePart("ww", Date()) - 2) and (Year(Date())*53 + DatePart("ww", Date()) - 1)

[tony30]

It is working nicely!! I don't get the logic of this particular expression,however the results are what I expect
Thank you so much!!
Tony

[RayMilhon]

The best explanation I can come up with is your original query uses the = sign so you got the records for the previous week. Your query for the 2 prior weeks uses the > sign so you got everything up to today. that's why you needed the between you needed to exclude the current week.