1 Reply Latest reply on May 13, 2013 2:32 PM by philmodjunk

    Date Ranges for Billing



      Date Ranges for Billing



           I have a table who's records represent projects ongoing at my workplace.  Within that table I have a start date field and a durration field.  Through a calculation I am able to find the end date.  What I would ultimately like to do is to set up a report that will put together a listing of all the projects happening that week.  So far I have been able to search based on the start or end date but the dates in between the start and end aren't associated with the record.  

           An example would be that if I had a project starting today 5/13/2013 and ending in three months on 8/13/2013 I can sucessfully search for it when entering 5/2013 or 8/2013 because the start and end values are within the month.  However when I search 7/2013 it comes up with no result.  

           Like I said above I would like to search by week.  I understand the best way to do that is by using "..." between any two dates.  Still if I can't locate everything in the month of July by typing 7/2013 I won't be able to do that.  

           Am I formatting things wrong?  What is the best way to show durration from one date to another?  I am sure this is a simple process but I am a bit of a beginer.

           Any help would be great.  Thanks!


        • 1. Re: Date Ranges for Billing

               I think that you'll need to generate several find requests with inequality operators to get what you need.

               A week represents a 7 (or 5 week day) range of dates. So each record being searched will have a date range of Date1...Date2 and the week specified will have a date range of DateA...DateB

               1) You want to find any record where Date1 falls between DateA and DateB


               2) any record where Date2 falls between DateA and DateB


               3) any record where DateA is after or equal to Date1 and DateB is Before or equal to Date2

               Examples. DateA...DateB might be 5/19/2013...5/25/2013

               1) 5/20/2013...7/30/2013
               2) 5/1/2013...5/25/2013
               3) 5/1/2013...9/29/2013

               A script can take a Date entered/selected in a global date field for DateA, add 7 to compute DateB and then generate 3 find requests in a single find to find records for cases 1, 2 and 3 above.

               Enter Find Mode []
               Set Field [YourTable::Date1 ; YourTable::gDateA & "..." & gDateA + 7 ]
               new record/Request
               Set Field [YourTable::Date2 ; YourTable::gDateA & "..." & gDateA + 7 ]
               New Record/Request
               Set Field [YourTable::Date1 ; "<" & YourTable::gDateA ]
               Set Field [YourTable::Date2 ; ">" & YourTable::gDateA + 7]
               Set Error Capture[on]
               Perform Find[]