Datediff Problem

[dazstarr]

Hi Guys

I need help on a problem I am having with the datediff function.

I have a table in my db which has 4 date fields:

DateRequested
DateReceived
DateReferred
DateOfAdmission

I have created the following module to work out weekdays (not weekends):
----------------------------------------------------------------------
Option Compare Database
Function DateDiffW(BegDate, EndDate)
Const SUNDAY = 1
Const SATURDAY = 7
Dim NumWeeks As Integer
If Not IsNull(BegDate) Then
If Not IsNull(EndDate) Then
If BegDate > EndDate Then
DateDiffW = 0
Else
Select Case Weekday(BegDate)
Case SUNDAY: BegDate = BegDate + 1
Case SATURDAY: BegDate = BegDate + 2
End Select
Select Case Weekday(EndDate)
Case SUNDAY: EndDate = EndDate - 2
Case SATURDAY: EndDate = EndDate - 1
End Select
NumWeeks = DateDiff("ww", BegDate, EndDate)
DateDiffW = NumWeeks * 5 + Weekday(EndDate) - Weekday(BegDate)
End If
End If
End If
End Function
-------------------------------------------------------------------

I have a query which works out the difference between any of these dates and it works fine.

My problem is that I would like know what the date would be 28 days from the DateRequested only including the weekdays.

I have tried entering DateDiffW + 28 in the query but it doesnt work.

Can anyone help me with this please.

Many Thanks in advance
Daz

 

[redneckgeek]

I think you'll have to create another function for that.
You'll have to count off how many TOTAL days there are in that 28 working day period.

 

[dazstarr]

New Function

Hi

Thanks for your reply.

The code I used for the DateDiffw function is old code that I recycled.

Any ideas on how to create this new function?

Many Thanks

 

[redneckgeek]

something like

Function 28DaysLater(RequestDate) as Date
dim x as integer, cnt as integer
x=1
cnt=0
do until cnt=28
   Select Case datepart("dw",dateadd("d",x,RequestDate))
   Case vbSaturday, vbSunday  ' these are constants already
      x=x+1
   Case Else
      cnt=cnt+1
      x=x+1
   End Case
Loop
28DaysLater=Dateadd("d",x,requestdate)
End Function

That's complete air code. You may have to tweak it a little to make it work.

 

[dazstarr]

Thanks

Thanks - i will give it a go. Not the best programmer so fingers crossed!!

Thanks again.

 

[dazstarr]

: (

I tried this but it stopped my other functions from working for some strange reason.

Help!

 

[raskew]

Hi -

Give this a try:

Function AddWkDays(varDate As Variant, numdays As Integer, _
Optional pexclude As String = "17") As Date
'*******************************************
'Purpose:   Simple, non-formula method of
'           adding weekdays to a given
'           date
'Coded by:  raskew
'Inputs:    ? AddWkdays("08/04/94", 10, "17")
'Output:    8/18/1994
'Note:      pexclude = the weekdays to exclude
'           (e.g., Sat = 7, Sunday = 1)
'*******************************************
Dim thedate As Date, n As Integer, incl As Boolean

thedate = DateValue(varDate)
incl = False
Do While InStr(pexclude, WeekDay((thedate) + 1)) > 0
  thedate = thedate + 1
  incl = True
Loop
thedate = thedate + IIf(incl = False, 1, 0)
n = 0
Do While n < numdays
   If InStr(pexclude, WeekDay(thedate)) = 0 Then
      n = n + 1
   End If
   If n = numdays Then Exit Do
   thedate = thedate + 1
Loop
AddWkDays = thedate
End Function

HTH - Bob

 

[dazstarr]

Hi

Thanks for the code - it's looking good but how do I implement this in my query please?

Many Thanks

 

[raskew]

After copying Function AddWkDays() to a standard module:

Here's an example using a copy of Northwind's Orders table:

SELECT Orders1.OrderID, Orders1.OrderDate, Orders1.RequiredDate,
Orders1.ShippedDate, [COLOR="Red"]AddWkDays([OrderDate],28,"17") AS Expr1[/COLOR]
FROM Orders1

;

Bob

 

[dazstarr]

Thanks!!

Thats awesome thanks!!! Works a treat.

1 question though - what was the purpose of the 17 at the end of the statement - i am not sure what it does and took it out and the query still works?

Thanks again

 

[redneckgeek]

Hi -

Give this a try:

Function AddWkDays(varDate As Variant, numdays As Integer, _
Optional pexclude As String = "17") As Date
'*******************************************
'Purpose:   Simple, non-formula method of
'           adding weekdays to a given
'           date
'Coded by:  raskew
'Inputs:    ? AddWkdays("08/04/94", 10, "17")
'Output:    8/18/1994
'Note:      pexclude = the weekdays to exclude
'           (e.g., Sat = 7, Sunday = 1)
'*******************************************
Dim thedate As Date, n As Integer, incl As Boolean
 
thedate = DateValue(varDate)
incl = False
Do While InStr(pexclude, WeekDay((thedate) + 1)) > 0
  thedate = thedate + 1
  incl = True
Loop
thedate = thedate + IIf(incl = False, 1, 0)
n = 0
Do While n < numdays
   If InStr(pexclude, WeekDay(thedate)) = 0 Then
      n = n + 1
   End If
   If n = numdays Then Exit Do
   thedate = thedate + 1
Loop
AddWkDays = thedate
End Function

HTH - Bob

Bob,
Just curious why you chose to use string functions instead of the date functions, especially since your dealing with date data anyway?
Not being critical, I'm just asking.

 

[raskew]

Optional pexclude As String = "17")

Days to exclude, e.g. "1" = Sunday, "7" = Saturday

The reason you returned the same result when you omitted the "17"
from the function call is that "17" is the default if no pexclude is
called.

Try it with:

? AddWkDays(date(), 28)
vs
? AddWkDays(date(), 28, "1")

...and you'll see the difference.

Bob

 

[dazstarr]

Thanks - its all over!!

Many Thanks

Thats awesome and works a treat!!

 

[namliam]

Some followup on this here:
http://www.access-programmers.co.uk/forums/showthread.php?p=738814&posted=1#post738814