Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2004
    Posts
    173

    Unanswered: How to Create Recurring Schedule Entries

    I have a need to create recurring entries for scheduled services using different criteria as follows:

    1. the user will select a customer (customerID)

    2.Select a Service (service 1, service 2, etc..)

    3.assign it to a crew (crew 1, crew 2, crew 3)

    4. then a start date - ex 1/1/2005 and an end Date 9/1/2005

    5. then pick a weekday Mon thru Sun - so let's say a user picks Tuesday

    6. then pick a frequency -weekly, bi-weekly or monthly

    Once this is done, i would like to populate a schedule table according to the criteria selected.


    so, let;s say that the following is selected:


    CustomerID - 100
    Service - Service 1
    Crew - Crew 2
    Start date - 1/1/2006
    End date - 9/1/2006
    Day - Tuesday
    Frequency- Weekly


    I would want to loop thru and create records for every tuesday, from 1/1/06 to 9/1/06.

    if bi-weekly were selected on Tues and Fri, then create records for each tues/fri from1/1/06 to 9/1/06

    If monthly and Wed were selected -then create records forthe 1st Wed of each month,etc...


    The problem I seem to be having is that i cannot figure out how to generate the code to loopthruand create weekly appointments, bi weekly,etc... on the desired day of the week.

    Any ideas are appreciated! - AB

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    some seriously-untested breakfast-time code goes like this:

    const fWeekly as integer = 1
    const fBiWeekly as integer = 2
    const fMonthly as integer = 3

    private sub makeDates(dtStart as date, dtEnd as Date, intFreq as integer, intDay as integer, optional intDay2 as integer = 0)
    dim dtNew as date
    dim dtBW as date
    dim intBW as integer
    '
    ' for this to work, dtStart needs to be the
    ' date of the first appointment in the series.
    '
    ' if this doesn't happen "naturally" from
    ' your form, make the adjustment here.
    '
    ' so, assuming the adjustment has been made...
    '
    dtNew = dtStart
    intBW = intDay2 - intDay
    select case intFreq
    case fWeekly
    do while dtNew <= dtEnd
    ' do something with dtNew here
    dtNew = dateadd("d", dtNew, 7)
    loop
    case fBiWeekly
    do while dtNew <= dtEnd
    ' do something with dtNew here
    dtBW = dateadd("d", dtNew, intBW)
    ' do something with dtBW here
    dtNew = dateadd("d", dtNew, 7)
    loop
    case else
    do while dtNew <= dtEnd
    ' do something with dtNew here
    dtNew = dateadd("m", dtNew, 1)
    loop
    end select
    end sub

    izy
    currently using SS 2008R2

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    if you need the adjustment.

    if weekday(dtStart) <> intDay then
    dtStart = dateadd("d", dtStart, intDay - weekday(dtStart))
    '
    'this may be out by +/- one day ----- experiment a bit
    '
    endif



    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    If monthly and Wed were selected -then create records for the 1st Wed of each month,etc...
    ooops! i didn't notice that this morning.


    --- revise the adjustment ---

    if intFreq <> fMonthly then
    'don't adjust monthly here
    if weekday(dtStart) <> intDay then
    dtStart = dateadd("d", dtStart, intDay - weekday(dtStart))
    '
    'this may be out by +/- one day ----- experiment a bit
    '
    endif
    endif


    --- revise case else ---

    '
    '
    'here we have the monthly case which you want to be always
    'the FIRST Xxxxday of the month - seems a bit strange to
    'me --- wont you get a very busy first week and an empty
    'rest of the month ????????
    '...anyhow - this is what you asked for:
    '
    '
    'intDay is 1...7 day of the week
    '
    'here comes the special adjustment for monthly:
    dtNew = dateserial(year(dtStart), month(dtStart), 1)
    'note that you could equally use
    'dateadd("d", dtStart, - day(dtStart) + 1)
    'but it is more difficult to read 6 months later
    '
    if weekday(dtNew) <= intDay then
    dtNew = dateadd("d", dtNew, intDay - weekday(dtNew))
    else
    dtNew = dateadd("d", dtNew, 7 - (weekday(dtNew) - intday))
    endif

    if dtNew < dtStart then
    dtNew = dateserial(year(dtStart), month(dtStart) + 1, 1)
    if weekday(dtNew) < intDay then
    dtNew = dateadd("d", dtNew, intDay - weekday(dtNew))
    else
    dtNew = dateadd("d", dtNew, 7 - (weekday(dtNew) - intday))
    endif
    endif

    'now we are ready to loop
    do while dtNew <= dtEnd
    '
    ' do something with dtNew here
    '
    ' ...and build the next dtNew:
    dtNew = dateadd("m", dtNew, 1)
    dtNew = dateserial(yeardtNew), month(dtNew), 1)
    if weekday(dtNew) <= intDay then
    dtNew = dateadd("d", dtNew, intDay - weekday(dtNew)
    else
    dtNew = dateadd("d", dtNew, 7 - (weekday(dtNew) - intday))
    endif
    loop

    !!! completely untested
    check out the dateadd() calcs - any/all of them could be
    wrong by + or - one day.

    izy

    LATER: all those dtNow in the original should have been dtNew.
    ...think i got them all this time round
    Last edited by izyrider; 01-21-06 at 11:49.
    currently using SS 2008R2

Posting Permissions

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