If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#Error in Results
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. -- Coach K "Knowledge is Power" |
#2
|
|||
|
|||
#Error in Results
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 http://I.Am/DougSteele (no e-mails, please!) "Coach K" wrote in message ... 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. -- Coach K "Knowledge is Power" |
#3
|
|||
|
|||
#Error in Results
-----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/AwUBSWe854echKqOuFEgEQIvrgCdFBGkjZPTzPrtAYDw9U7wQX B5OncAn0oo c0md4aFc/WsFQGiyM2+OhfaT =1MST -----END PGP SIGNATURE----- Coach K wrote: 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. |
#4
|
|||
|
|||
#Error in Results
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" wrote: -----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/AwUBSWe854echKqOuFEgEQIvrgCdFBGkjZPTzPrtAYDw9U7wQX B5OncAn0oo c0md4aFc/WsFQGiyM2+OhfaT =1MST -----END PGP SIGNATURE----- Coach K wrote: 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. |
#5
|
|||
|
|||
#Error in Results
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" wrote: 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 http://I.Am/DougSteele (no e-mails, please!) "Coach K" wrote in message ... 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. -- Coach K "Knowledge is Power" |
#6
|
|||
|
|||
#Error in Results
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 http://I.Am/DougSteele (no private e-mails, please) "Coach K" wrote in message ... 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" wrote: 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 http://I.Am/DougSteele (no e-mails, please!) "Coach K" wrote in message ... 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. -- Coach K "Knowledge is Power" |
Thread Tools | |
Display Modes | |
|
|