Custom Function

[Kerry Purdy]

Hi,

I am stuck with a date function, I think it is just something silly I am
missing, please could you advise.

Many thanks for your time.

Kerry
______________________________________
In a Module named Functions:-

Public Function NumbWorkingDays(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: NumbWorkingDays
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function does not account for holidays.
'....................................................................
On Error GoTo Err_WorkingDays

Dim intCount As Integer

StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above

intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate

Select Case Weekday(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
NumbWorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function
_________________________________
In my Query:-

Notice: NumbWorkingDays([training start date],[cancelled date])

 

[Ofer Cohen]

What is the problem?
Do you get an error message?
Is the calculation is wrong?

 

[Kerry Purdy]

Hi,

I forgot to mention what the problem was! Sorry!

When I run the query it is showing the values of 0 in my notice column.

Many thanks

Kerry

 

[Ofer Cohen]

Does it always return 0?

It could be that difference between dates fall on weekends and this is why
it returns 0
Mybe in some records you are missing one of the dates

--
Good Luck
BS"D

Hi,

I forgot to mention what the problem was! Sorry!

When I run the query it is showing the values of 0 in my notice column.

Many thanks

Kerry

Hi,

I am stuck with a date function, I think it is just something silly I am
missing, please could you advise.

Many thanks for your time.

Kerry
______________________________________
In a Module named Functions:-

Public Function NumbWorkingDays(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: NumbWorkingDays
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function does not account for holidays.
'....................................................................
On Error GoTo Err_WorkingDays

Dim intCount As Integer

StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above

intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate

Select Case Weekday(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
NumbWorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function
_________________________________
In my Query:-

Notice: NumbWorkingDays([training start date],[cancelled date])

 

[Kerry Purdy]

Hi Ofer

Thanks for the mail. No, its always returning a zero

Course Start Date Dur Cancelled Date Notice
course title 20/03/2007 2 12-Mar-07 0
course title 03/04/2007 4 02-Apr-07 0
course title 10/04/2007 4 16-Mar-07 0
course title 17/04/2007 4 19-Mar-07 0

Does it always return 0?

It could be that difference between dates fall on weekends and this is why
it returns 0
Mybe in some records you are missing one of the dates

--
Good Luck
BS"D

Hi,

I forgot to mention what the problem was! Sorry!

When I run the query it is showing the values of 0 in my notice column.

Many thanks

Kerry

Hi,

I am stuck with a date function, I think it is just something silly I am
missing, please could you advise.

Many thanks for your time.

Kerry
______________________________________
In a Module named Functions:-

Public Function NumbWorkingDays(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: NumbWorkingDays
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function does not account for holidays.
'....................................................................
On Error GoTo Err_WorkingDays

Dim intCount As Integer

StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above

intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate

Select Case Weekday(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
NumbWorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function
_________________________________
In my Query:-

Notice: NumbWorkingDays([training start date],[cancelled date])

 

[Dale Fye]

Kerry,

First, modify your function statement. I don't think you intend to actually
modify the value of StartDate that was called. The default in VB is ByRef,
which means that when you modify the value of a variable that is passed to a
function in the function, the value of that variable where ever it came from
will be modified. I'd change your declaration statement to read.

Public Function NumbWorkingDays(ByVal StartDate As Date, ByVal EndDate As
Date) As Integer

An alternative to this is to create a variable that is private to the
function (my preferred method) and use that in a For Next loop. I've taken
the liberty of adding a couple of parameters to allow you to include or
exclude the startdate and end date without modifying your code.

Public Function NumbWorkingDays(ByVal StartDate As Date, ByVal EndDate As
Date, _
Optional IncludeStartDate
as boolean = true, _
Optional IncludeEndDate
as boolean = true) As Integer
'....................................................................
' Name: NumbWorkingDays
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function does not account for holidays.

On Error GoTo Err_WorkingDays

Dim intCount As Integer
Dim dtLoop as date

intCount = 0
for dtLoop = StartDate + 1 + IncludeStartDate to EndDate -1 -
IncludeEndDate

Select Case Weekday(StartDate)
Case 2, 3, 4, 5, 6
intCount = intCount + 1
Case Else
'weekend, do nothing
End Select

Next dtLoop

NumbWorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err
Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select
End Function

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

Hi Ofer

Thanks for the mail. No, its always returning a zero

Course Start Date Dur Cancelled Date Notice
course title 20/03/2007 2 12-Mar-07 0
course title 03/04/2007 4 02-Apr-07 0
course title 10/04/2007 4 16-Mar-07 0
course title 17/04/2007 4 19-Mar-07 0

Does it always return 0?

It could be that difference between dates fall on weekends and this is why
it returns 0
Mybe in some records you are missing one of the dates

--
Good Luck
BS"D

Hi,

I forgot to mention what the problem was! Sorry!

When I run the query it is showing the values of 0 in my notice column.

Many thanks

Kerry

:

Hi,

I am stuck with a date function, I think it is just something silly I am
missing, please could you advise.

Many thanks for your time.

Kerry
______________________________________
In a Module named Functions:-

Public Function NumbWorkingDays(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: NumbWorkingDays
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function does not account for holidays.
'....................................................................
On Error GoTo Err_WorkingDays

Dim intCount As Integer

StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above

intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate

Select Case Weekday(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
NumbWorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function
_________________________________
In my Query:-

Notice: NumbWorkingDays([training start date],[cancelled date])

 

[Kerry Purdy]

Hi All,

I think I had my start date and cancelled date the wrong way around in the
query! Doh!

Thanks so much for your help. I have applied the change to the function
statement too so it was prob a mixture of both.

Thanks

Kerry

Kerry,

First, modify your function statement. I don't think you intend to actually
modify the value of StartDate that was called. The default in VB is ByRef,
which means that when you modify the value of a variable that is passed to a
function in the function, the value of that variable where ever it came from
will be modified. I'd change your declaration statement to read.

Public Function NumbWorkingDays(ByVal StartDate As Date, ByVal EndDate As
Date) As Integer

An alternative to this is to create a variable that is private to the
function (my preferred method) and use that in a For Next loop. I've taken
the liberty of adding a couple of parameters to allow you to include or
exclude the startdate and end date without modifying your code.

Public Function NumbWorkingDays(ByVal StartDate As Date, ByVal EndDate As
Date, _
Optional IncludeStartDate
as boolean = true, _
Optional IncludeEndDate
as boolean = true) As Integer
'....................................................................
' Name: NumbWorkingDays
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function does not account for holidays.

On Error GoTo Err_WorkingDays

Dim intCount As Integer
Dim dtLoop as date

intCount = 0
for dtLoop = StartDate + 1 + IncludeStartDate to EndDate -1 -
IncludeEndDate

Select Case Weekday(StartDate)
Case 2, 3, 4, 5, 6
intCount = intCount + 1
Case Else
'weekend, do nothing
End Select

Next dtLoop

NumbWorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err
Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select
End Function

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

Hi Ofer

Thanks for the mail. No, its always returning a zero

Course Start Date Dur Cancelled Date Notice
course title 20/03/2007 2 12-Mar-07 0
course title 03/04/2007 4 02-Apr-07 0
course title 10/04/2007 4 16-Mar-07 0
course title 17/04/2007 4 19-Mar-07 0

Does it always return 0?

It could be that difference between dates fall on weekends and this is why
it returns 0
Mybe in some records you are missing one of the dates

--
Good Luck
BS"D


:

Hi,

I forgot to mention what the problem was! Sorry!

When I run the query it is showing the values of 0 in my notice column.

Many thanks

Kerry

:

Hi,

I am stuck with a date function, I think it is just something silly I am
missing, please could you advise.

Many thanks for your time.

Kerry
______________________________________
In a Module named Functions:-

Public Function NumbWorkingDays(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name: NumbWorkingDays
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function does not account for holidays.
'....................................................................
On Error GoTo Err_WorkingDays

Dim intCount As Integer

StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above

intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate

Select Case Weekday(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
NumbWorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function
_________________________________
In my Query:-

Notice: NumbWorkingDays([training start date],[cancelled date])