CODEWITHSUNDEEP
sqlvbadatabasedatems-access
Esszed
Esszed

Reputation: 607

MS Access filter and show data from selected time period

I have a table into which I put dates how long a machine was out of order. Example:

id Machine Start date End date Number of days
1 3 10.02.2020 17.02.2020 07
2 1 03.04.2020 09.04.2020 06
3 2 25.10.2020 06.11.2020 12
4 3 28.11.2020 10.12.2020 12

Machine number corresponds to an ID in appropriate Machine's table.

For start I would like to have a query or form where I would select a machine from combo box and time period (date picker or type it) and it would return how many days the machine was out of order during that time.

So, if I select machine with ID 3 and time period from 01.01.2020 to 31.12.2020 it would show 19 days. If I select for example machine with ID 2 and time period from 01.11.2020 to 30.11.2020 it would return 6 days and so on.

How would you go about this in Access? I am new to Access and VBA.

Upvotes: 0

Views: 272

Answers (1)

June7
June7

Reputation: 21370

This is one way to produce desired output. Place function in a general module.

Function GetDaysDown(dteStart As Date, dteEnd As Date, intMac As Integer)
Dim rs As DAO.Recordset, intDays As Integer, dteDate As Date
Set rs = CurrentDb.OpenRecordset("SELECT StartDate, EndDate FROM Data WHERE Machine = " & intMac & _
                                 " AND (StartDate BETWEEN #" & dteStart & "# AND #" & dteEnd & _
                                 " # OR EndDate BETWEEN #" & dteStart & "# AND #" & dteEnd & "#)")
Do While Not rs.EOF
    dteDate = rs!startDate
    Do While dteDate < rs!EndDate And dteDate < dteEnd
        If dteDate >= dteStart And dteDate <= dteEnd Then
            intDays = intDays + 1
            'Debug.Print dteDate
        End If
        dteDate = dteDate + 1
    Loop
    rs.MoveNext
Loop
GetDaysDown = intDays
End Function

Call function from a textbox like: =GetDaysDown([tbxStart], [tbxEnd], [cbxMac])

For more info about using international date format, review http://allenbrowne.com/ser-36.html

Upvotes: 1

Related Questions