#Error in Results

  • Thread starter Thread starter Coach K
  • Start date Start date
C

Coach K

I have a query that calculates dates using the WorkingDays2 function. If one
of the fields does not have a date yet i get the #error in the record. What
do i need to add to make it a null value?

Here is the code:
Public Function WorkingDays2(startdate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

startdate = startdate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While startdate <= EndDate

rst.FindFirst "[HolidayDate] = #" & startdate & "#"
If Weekday(startdate) <> vbSunday And Weekday(startdate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

startdate = startdate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

And big thank you to Arvin Meyer for the code.
Thanks for your help in advance.
 
What do you want to return when that happens?

You need to change

Public Function WorkingDays2(startdate As Date, EndDate As Date) As Integer

to

Public Function WorkingDays2(startdate As Variant, EndDate As Variant) As
Integer

Then, inside the function, you need to check if either parameter is Null.
Put the following after the variable declarations, before the line of code
"Set DB = CurrentDb"

If IsNull(startdate) Or IsNull(EndDate) Then
WorkingDays2 = 0
Else

Put the following after the line of code "WorkingDays2 = intCount":

End If
 
Thanks!!! for the help Isearch for two trying to find a answer. You Gentleman
Rock!!

In addtion, Douglas you ask me " What do i want to return when happens?" I
would like the field to be blank. I am also using the this query for a report
that count anything less than or equal 15.

Here is my unbound Expression: =Count(IIf([TAT-TOTAL]<=15,0,Null))
--
Coach K
"Knowledge is Power"


MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

(1) I'd change the function's parameters to Variants, because the
function is being called by a Query that may pass NULL values to it; (2)
and, change the function to return a Variant, so it can return NULL;
(3) and, change the function to check for NULL parameters, so it will
return NULL when it doesn't have enough data to compute the work days.

(1) & (2):
Public Function WorkingDays2(startdate As Variant, _
EndDate As Variant) As Variant

(3):
Dim DB As DAO.Database

' >>>> Insert check right here

' Check for viable data
If IsNull(StartDate) OR IsNull(EndDate) Then
' Return NULL
Exit Function
End If

Just a sideline:
A Weirdness: If the function is run from VBA the Exit Function command
returns an Empty variable. If the function is run from a Query the Exit
Function command returns a NULL variable!
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSWe854echKqOuFEgEQIvrgCdFBGkjZPTzPrtAYDw9U7wQXB5OncAn0oo
c0md4aFc/WsFQGiyM2+OhfaT
=1MST
-----END PGP SIGNATURE-----


Coach said:
I have a query that calculates dates using the WorkingDays2 function. If one
of the fields does not have a date yet i get the #error in the record. What
do i need to add to make it a null value?

Here is the code:
Public Function WorkingDays2(startdate As Date, EndDate As Date) As Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

startdate = startdate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While startdate <= EndDate

rst.FindFirst "[HolidayDate] = #" & startdate & "#"
If Weekday(startdate) <> vbSunday And Weekday(startdate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

startdate = startdate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

And big thank you to Arvin Meyer for the code.
Thanks for your help in advance.
Click to expand...
Click to expand...
 
Douglas, I add your code to the one below now they are asking if one of the
date fields is blank do not show a zero or just leave it blank. I could have
a little help on what to do.
Thanks
--
Coach K
"Knowledge is Power"


Douglas J. Steele said:
What do you want to return when that happens?

You need to change

Public Function WorkingDays2(startdate As Date, EndDate As Date) As Integer

to

Public Function WorkingDays2(startdate As Variant, EndDate As Variant) As
Integer

Then, inside the function, you need to check if either parameter is Null.
Put the following after the variable declarations, before the line of code
"Set DB = CurrentDb"

If IsNull(startdate) Or IsNull(EndDate) Then
WorkingDays2 = 0
Else

Put the following after the line of code "WorkingDays2 = intCount":

End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Coach K said:
I have a query that calculates dates using the WorkingDays2 function. If
one
of the fields does not have a date yet i get the #error in the record.
What
do i need to add to make it a null value?

Here is the code:
Public Function WorkingDays2(startdate As Date, EndDate As Date) As
Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between
them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

startdate = startdate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While startdate <= EndDate

rst.FindFirst "[HolidayDate] = #" & startdate & "#"
If Weekday(startdate) <> vbSunday And Weekday(startdate) <> vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

startdate = startdate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

And big thank you to Arvin Meyer for the code.
Thanks for your help in advance.
Click to expand...
Click to expand...
 
Public Function WorkingDays2( _
startdate As Variant, _
EndDate As Date _
) As Variant
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays
' between them
' Note that this function has been modified to account for holidays.
' It requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

If IsNull(StartDate) Or IsNull(EndDate)

WorkingDays2 = Null

ElseIf (IsDate(StartDate) And IsDate(EndDate)) = False Then

WorkingDays2 = Null

Else

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] " & _
"FROM tblHolidays", bOpenSnapshot)

startdate = startdate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While startdate <= EndDate

rst.FindFirst "[HolidayDate] = " & _
Format(startdate, "\#yyyy\-mm\-dd\#")
Select Case Weekday(startdate)
Case vbSaturday, vbSunday
Case Else
If rst.NoMatch Then intCount = intCount + 1
End Select

startdate = startdate + 1

Loop

WorkingDays2 = intCount

End If

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Coach K said:
Douglas, I add your code to the one below now they are asking if one of
the
date fields is blank do not show a zero or just leave it blank. I could
have
a little help on what to do.
Thanks
--
Coach K
"Knowledge is Power"


Douglas J. Steele said:
What do you want to return when that happens?

You need to change

Public Function WorkingDays2(startdate As Date, EndDate As Date) As
Integer

to

Public Function WorkingDays2(startdate As Variant, EndDate As Variant) As
Integer

Then, inside the function, you need to check if either parameter is Null.
Put the following after the variable declarations, before the line of
code
"Set DB = CurrentDb"

If IsNull(startdate) Or IsNull(EndDate) Then
WorkingDays2 = 0
Else

Put the following after the line of code "WorkingDays2 = intCount":

End If


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Coach K said:
I have a query that calculates dates using the WorkingDays2 function. If
one
of the fields does not have a date yet i get the #error in the record.
What
do i need to add to make it a null value?

Here is the code:
Public Function WorkingDays2(startdate As Date, EndDate As Date) As
Integer
'....................................................................
' Name: WorkingDays2
' Inputs: StartDate As Date
' EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date: May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between
them
' Note that this function has been modified to account for holidays. It
requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays",
dbOpenSnapshot)

startdate = startdate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While startdate <= EndDate

rst.FindFirst "[HolidayDate] = #" & startdate & "#"
If Weekday(startdate) <> vbSunday And Weekday(startdate) <> vbSaturday
Then
If rst.NoMatch Then intCount = intCount + 1
End If

startdate = startdate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

And big thank you to Arvin Meyer for the code.
Thanks for your help in advance.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top