none
Merging Date and Time fields in table for datediff/dateadd calculation RRS feed

  • Question

  • I have a table that has one field for date and two others for start time and end time. Date field shows date in mm/dd/yyyy format and start and end times are hh:mm:ss AM/PM format.  The times correspond to the date for that row, unless it is after midnight, then it is implied it is next day

    I first had to calculate the hours total on that date, so I did:

    DateDiff('s', [Start Date], DateAdd('d',IIF([End Date]<[Start Date]1,0),[End Date]))/3600 and then just filtered by date range

    Now I need to do a total hours of the rows that are within a 24 hour window from a date/time.  IF start date is before the 24 hour window, then I need to just take the hours from within the 24 hour window.

    I am assuming I will need to create a concatenation of the date field and the start/end dates to do the date diff calculation, then I can do dateadd from 24 hours?

    Should I make a new field with the date/time to use or can I contactenate inline in the datediff function?

    Thanks!



    Tuesday, April 24, 2018 7:04 PM

Answers

  • I think I figured it out:

    Hours: IIf(IsNull([tbl_Hours]![Date]+[tbl_Hours]![Time In]),DateDiff('s',([tbl_Hours]![Date]+[tbl_Hours]![Time Out]),[QueryDate])/3600,IIf(([tbl_Hours]![Date]+[tbl_Hours]![Time Out])<DateAdd('h',-24,[QueryDate]),DateDiff('s',DateAdd('h',-24,[QueryDate]),DateAdd('d',IIf([tbl_Hours]![Date]+[tbl_Hours]![Time In]<[tbl_Hours]![Date]+[tbl_Hours]![Time Out],1,0),[tbl_Hours]![Date]+[tbl_Hours]![Time In]))/3600,DateDiff('s',[tbl_Hours]![Date]+[tbl_Hours]![Time Out],DateAdd('d',IIf([tbl_Hours]![Date]+[tbl_Hours]![Time In]<[tbl_Hours]![Date]+[tbl_Hours]![Time Out],1,0),[tbl_Hours]![Date]+[tbl_Hours]![Time In]))/3600))

    • Proposed as answer by Terry Xu - MSFT Thursday, April 26, 2018 2:31 AM
    • Marked as answer by JHarding08 Thursday, April 26, 2018 4:28 PM
    Wednesday, April 25, 2018 7:21 PM

All replies

  • The times correspond to the date for that row, unless it is after midnight, then it is implied it is next day

    If it's not too late, I would probably recommend storing the dates with the start and end time, so you'll only need two fields instead of two. When the end time goes past midnight, there is no assumption to be made since the date for the next day will be included in the field. This will also help make the calculation easier because you don't have to check if the time went past midnight to adjust for the next 24 hours.

    Just my 2 cents...

    Tuesday, April 24, 2018 7:24 PM
  • Thank you for the feedback. Unfortunately, I cannot change the schema of the table or the data.  I will have to do the modifications in the query.

    if, for example, date is 4/23/2018 and start time is 1:00 PM and end time is 3:00 PM and the query run time is 4/24/2018 2:00 PM and I need 24 hour window, do I need to include date in the datediff hours calculation?

    Tuesday, April 24, 2018 8:59 PM
  • Hi,

    Does the 24-hour period starts or ends at 4/24/18 2PM?

    Tuesday, April 24, 2018 9:12 PM
  • You might like to take a look at TimeArithmetic.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates the use of a number of functions for undertaking time arithmetic, including where the values are 'time only' and span midnight.

    Ken Sheridan, Stafford, England

    Tuesday, April 24, 2018 9:13 PM
  • 4/24/2018 2 pm would be the time the query is run and we would want to see all hours within 24 hours before that, so 4/23/2018 2 pm to 4/24/2018 2 pm .

    Can I use Cast in Access?

     Cast([Date] as DateTime) +  Cast([Start Time] as DateTime) as [Start DateTime]

    • Edited by JHarding08 Tuesday, April 24, 2018 9:47 PM
    Tuesday, April 24, 2018 9:33 PM
  • Can I use Cast in Access?

     Cast([Date] as DateTime) +  Cast([Start Time] as DateTime) as [Start DateTime]

    No, I don't think you can use CAST in Access. For a criteria, we can pull the past 24 hours from now by using something like:

    WHERE DateFieldName + TimeFieldName Between DateAdd("h",-24,Now()) And Now()

    Hope it helps...


    • Edited by .theDBguy Tuesday, April 24, 2018 9:52 PM
    Tuesday, April 24, 2018 9:51 PM
  • 4/24/2018 2 pm would be the time the query is run and we would want to see all hours within 24 hours before that, so 4/23/2018 2 pm to 4/24/2018 2 pm .


    To determine whether two date/time ranges intersect you can call the following function:

    Public Function WithinDateRange(dtmParamRangeStart As Date, _
                           dtmParamRangeEnd As Date, _
                           dtmDataRangeStart As Date, _
                           dtmDataRangeEnd As Date) As Boolean
                           
    ' Accepts: date at which parameter range starts
    '          date at which parameter range ends
    '          date at which data range starts
    '          date at which data range ends
    ' Returns: True if data range intersects with parameter range
    '          False if whole of data range outside parameter range
     
       WithinDateRange = _
           dtmDataRangeStart <= dtmParamRangeEnd And _
           dtmDataRangeEnd >= dtmParamRangeStart
           
    End Function

    With your example you could call it like this:

        Const NowTime As Date = #4/24/2018 2:00:00 PM#
        
        ' this will return True
        Debug.Print WithinDateRange(NowTime - 1, NowTime, #4/23/2018# + #1:00:00 PM#, #4/23/2018# + #3:00:00 PM#)
        ' this will return False
        Debug.Print WithinDateRange(NowTime - 1, NowTime, #4/23/2018# + #12:00:00 PM#, #4/23/2018# + #12:30:00 PM#)

    You would of course call the Now(0 function to return the date/time value when the query is opened, rather than the NowTime constant used for this example.

    In the above the first call of the WithinDateRange function passes values where the two ranges intersect, i.e. the 24 hours preceding the current date/time and the range defined by the start and end date/time values computed by adding the date to the time in each case.  You would of course need to add one day to the end of range date/time value where the start time is later than the end time, i.e. the times span midnight.   The function will consequently return a Boolean TRUE in this example.

    For the second function call the start and end times have been moved back so that the ranges no longer intersect.  The function will consequently return a Boolean FALSE.

    Calling the function in a query would identify those rows where the range falls completely or partially within the 24 hours preceding the current date/time.  To sum the total time from all intersecting ranges which fall within the 24 hour parameter range, however, you  would need to firstly determine the duration of the intersection, which in the first of the above examples would be one hour.  The durations of the intersections would then be summed.

    One thing to note is that the function as written above will return a True if the end of the parameter range and the start of the data range are exactly the same, i.e. contiguity of two ranges is regarded as an intersection.  If you wish to avoid this the code can be amended as follows:

       WithinDateRange = _
           dtmDataRangeStart < dtmParamRangeEnd And _
           dtmDataRangeEnd > dtmParamRangeStart


    Ken Sheridan, Stafford, England

    Tuesday, April 24, 2018 10:30 PM
  • Hello JHarding08,

    To be honestly, I'm still confused about your requirement. What do you want to do via the query?

    Based on your example, date is 4/23/2018 and start time is 1:00 PM and end time is 3:00 PM and the query run time is 4/24/2018 2:00 PM, what result do you want to get? What's the logic of it?

    Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Wednesday, April 25, 2018 2:19 AM

  • I am looking to get the total hours for each record that falls within that 24 hour period

    For example:

    Date - Person -  Time Out - Time In

    4/24/2018 - Person 1 - 9:30AM - 10:30AM

    4/24/2018 - Person 1 - 2:00PM - 3:00PM

    4/25/2018 - Person 1 - 9:00AM - NULL

    If the query is run on 4/25/208 at 10:00 AM, we get all three records returned because they all fall within that 24 hour window (time out to time in)

    PARAMETERS QueryDate DateTime;
    SELECT tbl_Hours.Person, tbl_Hours.Date, [tbl_Hours]![Date]+[tbl_Hours]![Time Out] AS [DateTimeOut as Datetime], [tbl_Hours]![Date]+[tbl_Hours]![Time In] AS [DateTimeIn as datetime]
    FROM tbl_Hours
    WHERE ((([tbl_Hours]![Date]+[tbl_Hours]![Time Out]) Between DateAdd('h',-24,[QueryDate]) And [querydate])) OR ((([tbl_Hours]![Date]+[tbl_Hours]![Time In]) Between DateAdd('h',-24,[QueryDate]) And [querydate]));

    Result

    Row - Person - Date - DateTimeOut - DateTimeIn

    Row 1 - Person 1 - 4/24/2018 - 4/24/2018 9:30 AM - 4/24/2018 10:30 AM

    Row 2 - Person 1 - 4/24/2018 - 4/24/2018 2:00 PM - 4/24/2018 3:00 PM

    Row 3 - Person 1 - 4/25/2018 - 4/25/2018 9:00 AM - NULL


    What I want to see if the total hours (time out-time in) for each record that falls within the window (4/24/2018 10:00 AM to 4/25/2018 10:00 AM)

    So for the three records above the hours would be calculated as:

    Row 1: Time out is before window, so take beginning of window (4/24/2018 10:00 Am) and calculate to time in (4/24/2018 10:30 AM) so total hours would be .5

    Row 2: time out and time in are in window , so take datediff of time out vs time in - 1 hr

    Row 3: time out is within window but no time in so take time out (4/25/2018 9:00 am) and calculate to end of window (4/25/2018) 10:00 AM - 1 hr

    Total for person would be 2.5 hrs in 24 hour window.

    The three scenarios are

    -time out-time in within window (time out-time in)

    -time out before window time in within window (window start-time in)

    -time out within window, no time in (time out-window end)

    I'm sure I'll have to do an IIF in access to account for all this.  how would I do it, just nest each scenario?


    Wednesday, April 25, 2018 6:05 PM
  • I think I figured it out:

    Hours: IIf(IsNull([tbl_Hours]![Date]+[tbl_Hours]![Time In]),DateDiff('s',([tbl_Hours]![Date]+[tbl_Hours]![Time Out]),[QueryDate])/3600,IIf(([tbl_Hours]![Date]+[tbl_Hours]![Time Out])<DateAdd('h',-24,[QueryDate]),DateDiff('s',DateAdd('h',-24,[QueryDate]),DateAdd('d',IIf([tbl_Hours]![Date]+[tbl_Hours]![Time In]<[tbl_Hours]![Date]+[tbl_Hours]![Time Out],1,0),[tbl_Hours]![Date]+[tbl_Hours]![Time In]))/3600,DateDiff('s',[tbl_Hours]![Date]+[tbl_Hours]![Time Out],DateAdd('d',IIf([tbl_Hours]![Date]+[tbl_Hours]![Time In]<[tbl_Hours]![Date]+[tbl_Hours]![Time Out],1,0),[tbl_Hours]![Date]+[tbl_Hours]![Time In]))/3600))

    • Proposed as answer by Terry Xu - MSFT Thursday, April 26, 2018 2:31 AM
    • Marked as answer by JHarding08 Thursday, April 26, 2018 4:28 PM
    Wednesday, April 25, 2018 7:21 PM
  • Hi,

    Glad to hear you got it sorted out. Good luck with your project.

    Wednesday, April 25, 2018 7:25 PM
  • Hi,

    Glad to hear you got it sorted out. Good luck with your project.

    Thank you. I am trying to open the query from a form with VBA.

    There are three parameters specified in the query that need to be specified when opening the query

    I tried docmd.openquery with docmd.setparameters, didnt work

    I tried dao.querydef and .execute, but says it cant execute a SELECT query

    What is the way to open the query in datasheet view from a command button click() event in vba.  

    Needs to work in access 2.0 as well :)

    Wednesday, April 25, 2018 11:02 PM
  • Hello JHarding,

    Since your original issue is resolved, I would suggest you mark your solution to close the current thread. If you have any other issue, please feel free to post a new thread for it.

    Thanks for understanding,

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 26, 2018 2:33 AM
  • What is the way to open the query in datasheet view from a command button click() event in vba.  

    Needs to work in access 2.0 as well :)

    Make the parameters references to controls in an unbound dialogue form.  In the dialogue form include a button to open the query.  In your current button's Click event procedure open the dialogue form, not the query.

    Ken Sheridan, Stafford, England

    Thursday, April 26, 2018 10:43 AM