Hello All,
I am using the function below that was written by Thom Rose to calculate the number of days between two dates.
The query results look like this:
CTC-RG RG-LC1 LC2-FG
34 23 22
#Error 67 31
21 #Error 2
65 34 4
The #Error shows up because there may have been a null date for that particular record and it was unable to calculate the days between. The funcition works well and the query provides the information I need, but when I created a delete and append table I receive the following error stating "Mircrosoft cant add all the records in the update or append query-due to a type conversion failure. The append query still adds the the records I need and does leave the records null that indicated #Error.
The constant pop up message "Microsoft Cant add all records... is annoying when I run the make and delete queries. Is there a way to bypass the message or ammend the funtion to allow the records to be appended without having the prompt appear?
Function WorkDaysDifference(dtStartDay As Date, dtEndDay As Date) As Long
'Function designed by Thom Rose - Permission to use is granted as long as you acknowledge the author
'This function calculates the number of workdays between two dates.
'The number of workdays is inclusive of the beginning and ending dates.
'There must be a table present called tblHolidays which contains the field dtObservedDate in the format date/time
'The table must list the dates of holidays to be observed.
'The user may include other fields in that table, for example, a description of the holiday being observed.
Dim lngTotalDays As Long
Dim lngTotalWeeks As Long
Dim dtNominalEndDay As Date
Dim lngTotalHolidays As Long
'Check to see if dtStartDay > dtEndDay. If so, then switch the dates
If dtStartDay > dtEndDay Then
dtNominalEndDay = dtStartDay
dtStartDay = dtEndDay
dtEndDay = dtNominalEndDay
End If
'Here are how many weeks are between the two dates
lngTotalWeeks = DateDiff("w", dtStartDay, dtEndDay)
'Here are the number of weekdays in that total week
lngTotalDays = lngTotalWeeks * 5
'Here is the date that is at the end of that many weeks
dtNominalEndDay = DateAdd("d", (lngTotalWeeks * 7), dtStartDay)
'Now add the number of weekdays between the nominal end day and the actual end day
While dtNominalEndDay <= dtEndDay
If WeekDay(dtNominalEndDay) <> 1 Then
If WeekDay(dtNominalEndDay) <> 7 Then
lngTotalDays = lngTotalDays + 1
End If
End If
dtNominalEndDay = dtNominalEndDay + 1
Wend
'Here are how many holiday days there are between the two days
lngTotalHolidays = DCount("dtObservedDate", "tblHolidays", "dtObservedDate <= #" & dtEndDay & "# AND dtObservedDate >= #" & dtStartDay & "# AND Weekday(dtObservedDate) <> 1 AND Weekday(dtObservedDate) <> 7")
'Here are how many total days are between the two dates - this is inclusive of the start and end date
WorkDaysDifference = lngTotalDays - lngTotalHolidays
End Function
I am using the function below that was written by Thom Rose to calculate the number of days between two dates.
The query results look like this:
CTC-RG RG-LC1 LC2-FG
34 23 22
#Error 67 31
21 #Error 2
65 34 4
The #Error shows up because there may have been a null date for that particular record and it was unable to calculate the days between. The funcition works well and the query provides the information I need, but when I created a delete and append table I receive the following error stating "Mircrosoft cant add all the records in the update or append query-due to a type conversion failure. The append query still adds the the records I need and does leave the records null that indicated #Error.
The constant pop up message "Microsoft Cant add all records... is annoying when I run the make and delete queries. Is there a way to bypass the message or ammend the funtion to allow the records to be appended without having the prompt appear?
Function WorkDaysDifference(dtStartDay As Date, dtEndDay As Date) As Long
'Function designed by Thom Rose - Permission to use is granted as long as you acknowledge the author
'This function calculates the number of workdays between two dates.
'The number of workdays is inclusive of the beginning and ending dates.
'There must be a table present called tblHolidays which contains the field dtObservedDate in the format date/time
'The table must list the dates of holidays to be observed.
'The user may include other fields in that table, for example, a description of the holiday being observed.
Dim lngTotalDays As Long
Dim lngTotalWeeks As Long
Dim dtNominalEndDay As Date
Dim lngTotalHolidays As Long
'Check to see if dtStartDay > dtEndDay. If so, then switch the dates
If dtStartDay > dtEndDay Then
dtNominalEndDay = dtStartDay
dtStartDay = dtEndDay
dtEndDay = dtNominalEndDay
End If
'Here are how many weeks are between the two dates
lngTotalWeeks = DateDiff("w", dtStartDay, dtEndDay)
'Here are the number of weekdays in that total week
lngTotalDays = lngTotalWeeks * 5
'Here is the date that is at the end of that many weeks
dtNominalEndDay = DateAdd("d", (lngTotalWeeks * 7), dtStartDay)
'Now add the number of weekdays between the nominal end day and the actual end day
While dtNominalEndDay <= dtEndDay
If WeekDay(dtNominalEndDay) <> 1 Then
If WeekDay(dtNominalEndDay) <> 7 Then
lngTotalDays = lngTotalDays + 1
End If
End If
dtNominalEndDay = dtNominalEndDay + 1
Wend
'Here are how many holiday days there are between the two days
lngTotalHolidays = DCount("dtObservedDate", "tblHolidays", "dtObservedDate <= #" & dtEndDay & "# AND dtObservedDate >= #" & dtStartDay & "# AND Weekday(dtObservedDate) <> 1 AND Weekday(dtObservedDate) <> 7")
'Here are how many total days are between the two dates - this is inclusive of the start and end date
WorkDaysDifference = lngTotalDays - lngTotalHolidays
End Function
