Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    May 2001
    Location
    MIA (takittodahous), Florida, USA
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date Projections (2002 SP-1)

    Hello,

    I've got to create a table (query) of date projections based on 1) A given start date 2) the current date and 3) given multiples. In my case I'm projecting the next occurrance of the multiples which are 1/4 annual, semi-annual, annual, etc. up to 5 years. So if the given start date is 1/1/2002 then I would create a record in the query that would read: 4/1/2003, 7/1/2003, 1/1/2004, 1/1/2004, 1/1/2005, 1/1/2006, 1/1/2007. Then I have another query which picks the appropriate date (column) based on a code.

    I run into trouble when I try to work the current date into a formula. I can't just do a date range from the given start date. I've been toying with applying a string for the year using IIf statements, but that isn't very efficient, is it.... nor have I gotten it to work accross the board (example above: the annual and bi-annual dates would have to be the same)

    Any ideas where to start looking? Has anyone tackled this kind of problem before? I haven't found much beyond date additions... here or in other sites.

    Thanks in advance for any and all help - y'all R da best!

  2. #2
    Star Lounger
    Join Date
    May 2001
    Location
    MIA (takittodahous), Florida, USA
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Projections (2002 SP-1)

    Hehe - maybe just wishful thinking on UAE - I hear the pay is great! thx 4 pointing it out...

    The object dates are projections - they occur in the future. So if my start date is 1/1/2002 - the first quarter occurring next (after today) would be 4/1/2003. Actually, since I posted, I've gotten the 1/4 column sussed out!! yaay!

    In the projection query I'm putting the record for a unique item with the starting date and fields for each of the projected dates as well as some other basis info. I'll check out Choose() and Switch() to see if that is a better tool for the selection processes - but I'm still wondering if there is some sort of defined function that can grab the nearest future base date.....

    Thanks for including your site link, too!

    Have a good weekend... I'll be here... crankin' away.. ;-]]

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Date Projections (2002 SP-1)

    <P ID="edit" class=small>(Edited by WendellB on 07-Mar-03 14:19. got distracted and failed to include correct post number)</P>You might consider using the Choose() fuinction or the Switch() function as alternatives to the IIF approach - there is a recent thread (<post#=232651>post 232651</post#>) dealing with a somewhat similar issue. But I don't understand what you sequence is supposed to look like. If I start with 1/1/2002, why is the next date 4/1/2003? And are you writing out a record for each date, or putting the dates in successive fields?

    <font color=blue>(BTW, are you really in the UAE, or should that be the USA?)</font color=blue>
    Wendell

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Date Projections (2002 SP-1)

    Actually, I think some date arithmetic may do the trick. To do a quarter add 92 days, and then reset to the first day of the month using the DateSerial() function. To do half a year, add 184 days and reset to the first day of the month/year that generates. A year is easier as you can do a DateAdd() and then compute DateSerial for 1/1 of that year, and so on. I guess the only question I have is how you treat it if the current date is 4/1/2003? (Yes, I know - April Fool!)
    Wendell

  5. #5
    Star Lounger
    Join Date
    May 2001
    Location
    MIA (takittodahous), Florida, USA
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Projections (2002 SP-1)

    Lol - thanks for the tips. I'll update with what I get...

  6. #6
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date Projections (2002 SP-1)

    A variation on Wendell's suggestion that may help is to use variations on the DateSerial function by adding multiples of 3 (quarters) or 6 (semi-annuals) to the month argument. For years, just add multiples to the year argument.

    For example, DateSerial(Year(Date), Month(Date) + 3, 1) produces a first-of-the-month 3 months out, DateSerial(Year(Date), Month(Date) + 6, 1) for 6 months out, DateSerial(Year(Date) + 2, Month(Date) , 1) for 2 years out, etc. DateSerial() seems to be quite forgiving regarding the use of >12 for the month argument.; for example DateSerial(Year(Date), Month(Date) + 18, 1) correctly calculates out to 9/1/04 (where Date is today, 3/7/03).

    I'm not sure where you're putting these dates (separate fields in one record or one field, separate records) or where you are putting the formulas, but maybe this will help some, nonetheless.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •