Roman F
asked on
I really need your help with access query
I have a table
let say that the fields are:
Rec#
StartDate
Status and so...
i need to calculate the number of days between StartDate and today
in excel i will use NETWORKDAYS(Date1,now(), list of holidays from the holiday table ) function with three arguments, what is the equivalent in access
i have a simple database, could you help me to fix my query to include business days only count
DBBusinessDate.accdb
let say that the fields are:
Rec#
StartDate
Status and so...
i need to calculate the number of days between StartDate and today
in excel i will use NETWORKDAYS(Date1,now(), list of holidays from the holiday table ) function with three arguments, what is the equivalent in access
i have a simple database, could you help me to fix my query to include business days only count
DBBusinessDate.accdb
what is expected result set?
ASKER
Rec# RecInd StartDate NumberofDays Expexted (minus weekends and holidays)
1 125 1/5/2017 140 98
2 1254 1/9/2017 136 96
3 325 2/21/2017 93 67
4 998 4/13/2017 42 30
5 5656 5/11/2017 14 10
1 125 1/5/2017 140 98
2 1254 1/9/2017 136 96
3 325 2/21/2017 93 67
4 998 4/13/2017 42 30
5 5656 5/11/2017 14 10
you should read this and implement it in your solution
https://msdn.microsoft.com/en-us/library/dd327646(v=office.12).aspx
https://msdn.microsoft.com/en-us/library/dd327646(v=office.12).aspx
ASKER
i saw the article, i need real help, please
ASKER
thank you for your response, but if you do not have an intention to help, do not post anything...
anyways, I implemented this
and used this query
result
Public Function CountWeekendDays(Date1 As Date, Date2 As Date) As Long
Dim StartDate As Date, EndDate As Date, _
WeekendDays As Long, i As Long
If Date1 > Date2 Then
StartDate = Date2
EndDate = Date1
Else
StartDate = Date1
EndDate = Date2
End If
WeekendDays = 0
For i = 0 To DateDiff("d", StartDate, EndDate)
Select Case Weekday(DateAdd("d", i, StartDate))
Case 1, 7
WeekendDays = WeekendDays + 1
End Select
Next
CountWeekendDays = WeekendDays
End Function
and used this query
SELECT r.[Rec#], r.RecInd, Format(Now()-[StartDate]-CountWeekendDays([StartDate],Now()) -(select count(1) from tblHolidays h where h.holidaydate between [StartDate] and now()) ,"#,###") AS NumberofDays
FROM tblRecords AS r;
result
Rec# RecInd NumberofDays
1 125 98
2 1254 96
3 325 67
4 998 30
5 5656 10
DBBusinessDate.accdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
that is how i learn, sorry for offensive words, you may think
now, i will look at the code and will understand,,,,
Thank you,
now, i will look at the code and will understand,,,,
Thank you,
ASKER
thank you very much