Link to home
Start Free TrialLog in
Avatar of Roman F
Roman FFlag for United States of America

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
Avatar of HainKurt
HainKurt
Flag of Canada image

what is expected result set?
Avatar of Roman F

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
you should read this and implement it in your solution

https://msdn.microsoft.com/en-us/library/dd327646(v=office.12).aspx
Avatar of Roman F

ASKER

i saw the article, i need real help, please
Avatar of Roman F

ASKER

thank you for your response, but if you do not have an intention to help, do not post anything...
anyways, I implemented this
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

Open in new window


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;

Open in new window


result

Rec#	RecInd	NumberofDays
1	125	98
2	1254	96
3	325	67
4	998	30
5	5656	10

Open in new window

DBBusinessDate.accdb
ASKER CERTIFIED SOLUTION
Avatar of HainKurt
HainKurt
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Roman F

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,
Avatar of Roman F

ASKER

thank you very much