Need help to look at code

#1 (**permalink**) 28-Jul-2006, 08:37 AM

Hi

I need to create a function that gets 3 working days before a workshop date.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/13427-need-help-to-look-at-code.html
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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13427
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 (**permalink**) 28-Jul-2006, 08:37 AM

Rather than try to find the flaw in the algorithm, why not simply grab code
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13427
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" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13427
> 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
>
>

#3 (**permalink**) 28-Jul-2006, 08:37 AM

Many thanks Doug

Will take a look at the code
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13427

Richard

"Douglas J. Steele" 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" 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13427
> > getReturnDate = dte
> >
> > ExitHere:
> > Exit Function
> > ErrHandler:
> > MsgBox Err.Description, , Err.Number
> > Resume ExitHere
> > End Function
> >
> >
>
>

#4 (**permalink**) 28-Jul-2006, 08:37 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13427
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" 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.
>
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13427
> 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 (**permalink**) 28-Jul-2006, 08:37 AM

Thanks Chris

I will try it out

Richard

"ChrisM" 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" wrote in message
> news:%23tMZyElqGHA.2108@TK2MSFTNGP03.phx.gbl...
> > Hi
> >
> > I need to create a function that gets 3 working days before a workshop
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13427
> > 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13427
> >
> > 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
> >
> >
>
>