X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Function Calculating Number Of Days Off, Access 2013    
post Oct 12 2017, 02:41 PM

Posts: 14
Joined: 15-August 17

Hello everybody!
I need to create a report with a field that calculated the days off ( DateIn-DateOut)between a period of time (DateFrom-DateTo). I am new in access so I have little knowledge so if you can give me a hand I would appreciate. My idea was to base the report on a query that selects only the records between the dates on the interval.
DateIn-DateOut: These are the first and last day the employee is not working.
DateFrom-DateTo: These dates are selected in my fdlgReportDateRange
I have 5 cases:
The first and the second are not selected in my query, so far so good:

1. The period of time is before the interval selected in my ReportDateRange
2. The period of time is after the interval selected in my ReportDateRange

In the following, I need to count only the days off in the interval:

3. DateIn is <=DateTo and DateOut is >DateTo --------DaysOff= DateDiff("d";[DateFrom];[DateOut])
4. DateIn is <=DateTo and DateOut is <DateFrom --------DaysOff= DateDiff("d";[DateIn];[DateTo])
5 DateIn is >DateTo and DateOut is <DateFrom --------DaysOff = DateDiff("d";[DateIn];[DateOut])

Maybe the best solution is using SQL in the query. However, I'm thinking about using a function in the report. I have tried to do it but I need some help with it, maybe something like this:

Function NumberDaysOff (DateIn as Date, DateOut as Date, DateFrom as Date, DateTo as Date)
Dim NumberDaysOff as Integer
Select Case 1
Me. DateIn is <=DateTo and Me.DateOut is >DateTo Then
Select Case 2
DateIn is <=DateTo and DateOut is <DateFrom
NumberDaysOffDaysOff= DateDiff("d";[DateIn];[DateTo])
Select Case 3
DateIn is >DateTo and DateOut is <DateFrom
NumberDaysOff = DateDiff("d";[DateIn];[DateOut])
End Select
End Function

Thanks in advance notworthy.gif

Go to the top of the page
post Oct 12 2017, 03:16 PM

Posts: 2,181
Joined: 27-February 09

create a calendar table with all the days you need. then just filter out weekends etc if you want. then it's just a pure count. No silly slow VBA garbage.
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    17th October 2017 - 07:02 PM