Need help to look at code

[Richard]

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

 

[Douglas J. Steele]

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

 

[Richard]

Many thanks Doug

Will take a look at the code

Richard

 

[ChrisM]

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]

Thanks Chris

I will try it out

Richard

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.