Performing a find by date ranging using weeks

[manatee]

I am trying to perform a find based on a date range. However, this range is based on weeks out. For example, Today is 8/16/07. I want to find all records between 2weeks from now, and 6 weeks out. I currently have this script:

Let (cd = Get ( CurrentDate ) ; WeekOfYear ( cd )+2 & "..." & WeekOfYear ( cd )+6 )

However, I am not certain how to turn the week back into a date range for the field. I am thinking I am missing something simple, but am stumped.

[Ender]

How about:

Enter Find Mode []

Set field [ Date ; getasdate(get(currentdate) + 14) & "..." & getasdate(get(currentdate) + 42) ]

Perform Find []

[manatee]

That looks good except a concern is that we will miss some records as the dates we are searching are all on Fridays, and depending upon when the script is ran, some records will be missed at the backend. So I thought going with weeks would ensure that all records are found. So are weeks pretty much not an option?

[Ender]

Sorry man, you've lost me. You wanted 2 weeks from now to 6 weeks from now, right?

[manatee]

Sorry for the chaos. You are correct, I am looking for the records between 2 weeks out and 6 weeks out. And the field I am searching in is a standard date field. Maybe I should create a week field based on the date?

[Ender]

Still not tracking. If you ran that script I provided, you'd find records between Thursday, August 30th and Thursday, September 27th (inclusive). Why do you need to create a week field?

[manatee]

All of the dates we search for fall on a Friday. So if we run a script on a Saturday that looks for Today plus 14, it will not count in the current week. So we went for weeks that way all dates are covered and none are missed. Hope that helps. I appreciate the help.

[Fenton]

_cFri_next = Get (CurrentDate) - Mod ( Get (CurrentDate); 7 ) + 5

(thanks to Søren)

or

_cFri_next = Int ( Date_/7 ) * 7 + 5

(thanks to Andrew Cates)

or

mine, which was less elegant, so I won't post it :-]

or

_cFri_last = Date_ - Mod ( Date_ ; 7 ) - 2

Bascially:

_cSun_last = Date_ - Mod ( Date_ ; 7 )

Use one of the above as a starting point for your calculation.

BTW, I was just considering WeekOfYear, and realized that it has a slight glitch when used as you were thinking (despite the year glitch itself). A day in the middle of the week could be in week 53 of a year (Mon 12/31/2007), whereas the next day could be in week 1 of the next year (Tues 1/1/2008). In other words it starts over at 1 the first day of the year, no matter what day it is. It is not consistently 7 days long.

[manatee]

I am confident that those are great starts, but regrettably, I do not understand how to incorporate those. A bit out of my league. Are those for "Let" functions?

Sorry, and appreciate the help.

[CobaltSky]

I am confident that those are great starts, but regrettably, I do not understand how to incorporate those. A bit out of my league. Are those for "Let" functions?

Sorry, and appreciate the help.

manatee,

It seems to me that the problem you've described is pretty simple to solve - or at least it would be if you described it clearly.

You've repeatedly used phrases such as "2 weeks out" without ever actually saying what you mean by that. Then you've said that the result has to include a Friday - but you haven't said *which* Friday with respect to the current date or the current week. Talking in such vague and general terms is pretty unhelpful and just leaves us all guessing.

I suggest that you provide some explicit examples giving dates and stating unequivocally: if the current date is anywhere within week x to y, you want a search range that will encompass Fridays a, b, c and d. That's six specific dates required to make your example clear.

Once you've clarified what it is you're actually asking, I'm sure there are any number of us here who'll be able to tell you ways to achieve it. Until then, it is all just thrashing around in the dark. :wink2:

[manatee]

I regret the lack of clarity as it was not intended and the help is appreciated. Let me see if I can clear things up.

We have records that have a sale date that falls on Fridays. All of our searches are based on that date. What we are needing to do is find ALL records that have a Sale Date that falls between 2 weeks from "Current Date" and 6 weeks from "Current Date". In the end the search should find all records that are with the 4 week range 2 weeks out from the current date (date of search).

I think I found a cheesy way of doing it, but I am not sure if it is best. What I did was create a calc field (sale_week) that was defined as WeekofYear(sale_date) That way I now have the week defined for the sale date. Then I created a Set Field step in the search script where I set the sale_week field to be (Sale_week)+2 &"..."& (sale_week)+6 I believe it is not quite accurate but was pointing in the right direction.

Does that make more sense?

[mr_vodka]

It still seems a little vague, but if I am correctly understanding your posts, I think that you want to find all records that are 2 to 6 weeks out, starting from the FRIDAY of the current week.

Try this.

Set Variable [ $d; Value:Let ( d = Get (CurrentDate); d - DayOfWeek ( d ) + 6 ) ]

Enter Find Mode []

Set Field [ YourDateField; $d + 14 & "..." & $d + 42 ]

Perform Find []