Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Need help to look at code

Discussion in 'Information Technology' started by Richard, Jul 28, 2006.

Tags:
  1. Richard

    Richard
    Expand Collapse
    Guest

    Hi

    I need to create a function that gets 3 working days before a workshop date.
    I created the following code but doesn't seem get what I need.

    If today is Tuesday (18/07/2006), and I type getReturnDate(Date(),1), I get
    17/07/2006. Correct.

    But when I type getReturnDate(Date(),2), I get 16/07/06 which is a Sunday.

    Please check and many thanks in advance.

    Richard


    Public Function getReturnDate(WORKSHOPDATE As Date, intWorkingDays As
    Integer) As Date
    On Error GoTo ErrHandler
    Dim i As Integer
    Dim dte As Date

    i = 0
    dte = WORKSHOPDATE

    Do Until i = intWorkingDays

    Select Case Weekday(dte)
    Case Is = 1, 7
    dte = dte - 1
    Case Is = 2, 3, 4, 5, 6
    dte = dte - 1
    i = i + 1
    End Select
    Loop
    getReturnDate = dte

    ExitHere:
    Exit Function
    ErrHandler:
    MsgBox Err.Description, , Err.Number
    Resume ExitHere
    End Function
     
  2. Loading...

    Similar Threads Forum Date
    Interracial And Interfaith Marriage, Help Needed To Confront/convince My Parents Love & Marriage Aug 16, 2016
    17 yr old Baljit needs your help Get Involved Aug 19, 2015
    Learn Punjabi Need help Language, Arts & Culture May 20, 2014
    United Sikhs From United Sikhs. Phillippines Needs Your Help Sikh Organisations Nov 22, 2013
    Need Help... (Keeping Hair) Questions and Answers Sep 2, 2013

  3. Douglas J. Steele

    Douglas J. Steele
    Expand Collapse
    Guest

    Rather than try to find the flaw in the algorithm, why not simply grab code
    that does work?

    Check, for example, http://www.mvps.org/access/datetime/date0012.htm at "The
    Access Web", or my September, 2004 "Access Answers" column in Pinnacle
    Publication's "Smart Access". You can download the column (and sample
    database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Richard" <richardbee@hotmail.com> wrote in message
    news:%23tMZyElqGHA.2108@TK2MSFTNGP03.phx.gbl...
    > Hi
    >
    > I need to create a function that gets 3 working days before a workshop

    date.
    > I created the following code but doesn't seem get what I need.
    >
    > If today is Tuesday (18/07/2006), and I type getReturnDate(Date(),1), I

    get
    > 17/07/2006. Correct.
    >
    > But when I type getReturnDate(Date(),2), I get 16/07/06 which is a Sunday.
    >
    > Please check and many thanks in advance.
    >
    > Richard
    >
    >
    > Public Function getReturnDate(WORKSHOPDATE As Date, intWorkingDays As
    > Integer) As Date
    > On Error GoTo ErrHandler
    > Dim i As Integer
    > Dim dte As Date
    >
    > i = 0
    > dte = WORKSHOPDATE
    >
    > Do Until i = intWorkingDays
    >
    > Select Case Weekday(dte)
    > Case Is = 1, 7
    > dte = dte - 1
    > Case Is = 2, 3, 4, 5, 6
    > dte = dte - 1
    > i = i + 1
    > End Select
    > Loop
    > getReturnDate = dte
    >
    > ExitHere:
    > Exit Function
    > ErrHandler:
    > MsgBox Err.Description, , Err.Number
    > Resume ExitHere
    > End Function
    >
    >
     
  4. Richard

    Richard
    Expand Collapse
    Guest

    Many thanks Doug

    Will take a look at the code

    Richard

    "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_canada.com> wrote in message
    news:%23lZdqNlqGHA.2304@TK2MSFTNGP03.phx.gbl...
    > Rather than try to find the flaw in the algorithm, why not simply grab

    code
    > that does work?
    >
    > Check, for example, http://www.mvps.org/access/datetime/date0012.htm at

    "The
    > Access Web", or my September, 2004 "Access Answers" column in Pinnacle
    > Publication's "Smart Access". You can download the column (and sample
    > database) for free at http://www.accessmvp.com/DJSteele/SmartAccess.html
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Richard" <richardbee@hotmail.com> wrote in message
    > news:%23tMZyElqGHA.2108@TK2MSFTNGP03.phx.gbl...
    > > Hi
    > >
    > > I need to create a function that gets 3 working days before a workshop

    > date.
    > > I created the following code but doesn't seem get what I need.
    > >
    > > If today is Tuesday (18/07/2006), and I type getReturnDate(Date(),1), I

    > get
    > > 17/07/2006. Correct.
    > >
    > > But when I type getReturnDate(Date(),2), I get 16/07/06 which is a

    Sunday.
    > >
    > > Please check and many thanks in advance.
    > >
    > > Richard
    > >
    > >
    > > Public Function getReturnDate(WORKSHOPDATE As Date, intWorkingDays As
    > > Integer) As Date
    > > On Error GoTo ErrHandler
    > > Dim i As Integer
    > > Dim dte As Date
    > >
    > > i = 0
    > > dte = WORKSHOPDATE
    > >
    > > Do Until i = intWorkingDays
    > >
    > > Select Case Weekday(dte)
    > > Case Is = 1, 7
    > > dte = dte - 1
    > > Case Is = 2, 3, 4, 5, 6
    > > dte = dte - 1
    > > i = i + 1
    > > End Select
    > > Loop
    > > getReturnDate = dte
    > >
    > > ExitHere:
    > > Exit Function
    > > ErrHandler:
    > > MsgBox Err.Description, , Err.Number
    > > Resume ExitHere
    > > End Function
    > >
    > >

    >
    >
     
  5. ChrisM

    ChrisM
    Expand Collapse
    Guest

    Bit late now that Douglas has pointed you to some working functions, but I
    had already started looking at this, and I usually have to finish somthing
    once I've started...

    The bug in you code is quite simple.
    In your loop where you determine whether the day is a working day, you
    should be looking one day earlier. That is if you are looking for 1 day
    before, you need to find out if YESTERDAY was a working day, not today. 2
    Days earlier, you need to know if yesterday and the day before were working
    days, not today and yesterday...

    Change
    'Select Case Weekday(dte)'
    to
    'Select Case Weekday(dte-1)'

    and I think it should work how you want.

    Cheers,

    Chris.

    "Richard" <richardbee@hotmail.com> wrote in message
    news:%23tMZyElqGHA.2108@TK2MSFTNGP03.phx.gbl...
    > Hi
    >
    > I need to create a function that gets 3 working days before a workshop
    > date.
    > I created the following code but doesn't seem get what I need.
    >
    > If today is Tuesday (18/07/2006), and I type getReturnDate(Date(),1), I
    > get
    > 17/07/2006. Correct.
    >
    > But when I type getReturnDate(Date(),2), I get 16/07/06 which is a Sunday.
    >
    > Please check and many thanks in advance.
    >
    > Richard
    >
    >
    > Public Function getReturnDate(WORKSHOPDATE As Date, intWorkingDays As
    > Integer) As Date
    > On Error GoTo ErrHandler
    > Dim i As Integer
    > Dim dte As Date
    >
    > i = 0
    > dte = WORKSHOPDATE
    >
    > Do Until i = intWorkingDays
    >
    > Select Case Weekday(dte)
    > Case Is = 1, 7
    > dte = dte - 1
    > Case Is = 2, 3, 4, 5, 6
    > dte = dte - 1
    > i = i + 1
    > End Select
    > Loop
    > getReturnDate = dte
    >
    > ExitHere:
    > Exit Function
    > ErrHandler:
    > MsgBox Err.Description, , Err.Number
    > Resume ExitHere
    > End Function
    >
    >
     
  6. Richard

    Richard
    Expand Collapse
    Guest

    Thanks Chris

    I will try it out

    Richard

    "ChrisM" <chris_mayersblue@suedeyahoo.com> wrote in message
    news:WeSdnT0uXoDwJyHZnZ2dnUVZ8tOdnZ2d@bt.com...
    > Bit late now that Douglas has pointed you to some working functions, but I
    > had already started looking at this, and I usually have to finish somthing
    > once I've started...
    >
    > The bug in you code is quite simple.
    > In your loop where you determine whether the day is a working day, you
    > should be looking one day earlier. That is if you are looking for 1 day
    > before, you need to find out if YESTERDAY was a working day, not today. 2
    > Days earlier, you need to know if yesterday and the day before were

    working
    > days, not today and yesterday...
    >
    > Change
    > 'Select Case Weekday(dte)'
    > to
    > 'Select Case Weekday(dte-1)'
    >
    > and I think it should work how you want.
    >
    > Cheers,
    >
    > Chris.
    >
    > "Richard" <richardbee@hotmail.com> wrote in message
    > news:%23tMZyElqGHA.2108@TK2MSFTNGP03.phx.gbl...
    > > Hi
    > >
    > > I need to create a function that gets 3 working days before a workshop
    > > date.
    > > I created the following code but doesn't seem get what I need.
    > >
    > > If today is Tuesday (18/07/2006), and I type getReturnDate(Date(),1), I
    > > get
    > > 17/07/2006. Correct.
    > >
    > > But when I type getReturnDate(Date(),2), I get 16/07/06 which is a

    Sunday.
    > >
    > > Please check and many thanks in advance.
    > >
    > > Richard
    > >
    > >
    > > Public Function getReturnDate(WORKSHOPDATE As Date, intWorkingDays As
    > > Integer) As Date
    > > On Error GoTo ErrHandler
    > > Dim i As Integer
    > > Dim dte As Date
    > >
    > > i = 0
    > > dte = WORKSHOPDATE
    > >
    > > Do Until i = intWorkingDays
    > >
    > > Select Case Weekday(dte)
    > > Case Is = 1, 7
    > > dte = dte - 1
    > > Case Is = 2, 3, 4, 5, 6
    > > dte = dte - 1
    > > i = i + 1
    > > End Select
    > > Loop
    > > getReturnDate = dte
    > >
    > > ExitHere:
    > > Exit Function
    > > ErrHandler:
    > > MsgBox Err.Description, , Err.Number
    > > Resume ExitHere
    > > End Function
    > >
    > >

    >
    >
     

Share This Page