Results 1 to 8 of 8
  1. #1
    Verso is offline Novice
    Windows Vista Access 2007
    Join Date
    Aug 2011
    Posts
    6

    Timing Query

    Hello,

    I work in the freight train industry and I have built a performance database based on departures and arrivals (Dep & Arr).

    Departure and arrivals times are added to their respective fields and I have created the following to distinguish between RT (Right Time) and LATE:-

    Minutes: DateDiff("n",[Planned Dep],[Actual Dep]) - This tells me how minutes late or early a train was
    RT or L: IIf([Minutes]<=0,"RT",IIf([Minutes]>0,"LATE")) - This returns either RT or LATE (if a train is early it's still classed as RT)

    and then similar for Arr



    All working OK except:-

    If I have a train that is due to depart at 00:01 and departs at 23:54 I want to see -7 and RT but I'm seeing 1433 and LATE


    My times are just TIMES and don't include any DATES - Is there a way I can fix this without adding in actual dates?

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    ALL times must include dates, that is why the field can accept Date & Time.
    Then you can calculate elasped time.

  3. #3
    Verso is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2011
    Posts
    6
    Oh well - Almost 2 years worth of data. Not going to be able to do that then.........

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please show your table designs and some sample records.

  5. #5
    Verso is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2011
    Posts
    6
    Click image for larger version. 

Name:	1.png 
Views:	8 
Size:	93.2 KB 
ID:	40350Click image for larger version. 

Name:	2.png 
Views:	8 
Size:	102.2 KB 
ID:	40351

    1) Shows the data as I'd like it
    2) Shows how the data doesn't allow for 00:00 following 23:00

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I mocked up a few of your records in a table called tblTrain.

    Click image for larger version. 

Name:	tblTrainDesign.PNG 
Views:	6 
Size:	8.5 KB 
ID:	40353

    with some records

    ID Planned Dep Actual Dep RT or Late OtherStuff
    1 00:01 23:54

    2 06:15 06:18

    3 06:25 06:17

    4 08:48 08:43

    5 08:50 09:13

    6 14:19 17:29

    7 08:12 11:28


    Then created this Query

    Code:
    SELECT tblTrain.ID, tblTrain.[Planned Dep], tblTrain.[Actual Dep]
    , IIf(DateDiff("n",[Planned Dep],[Actual Dep])>1300,DateDiff("n",[Planned Dep],[Actual Dep])-1440,DateDiff("n",[Planned Dep],[Actual Dep])) AS Minutes
    , IIf([Minutes]<=0,"RT","LATE") AS [RT or Late]
    FROM tblTrain;
    To produce:
    Click image for larger version. 

Name:	TrainEarlyOrLate.PNG 
Views:	7 
Size:	23.1 KB 
ID:	40352

    I hope it's useful to you.

    Good luck.

  7. #7
    Verso is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2011
    Posts
    6
    You Sir are a legend

    Thank you. Works a treat

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Calculation timing in Form/Subform
    By GregShah in forum Forms
    Replies: 1
    Last Post: 09-10-2019, 07:06 AM
  2. Moderator: Timing of Thread Notifications?
    By Micron in forum Forum Suggestions
    Replies: 5
    Last Post: 05-07-2018, 07:23 PM
  3. Timing issue
    By aytee111 in forum Modules
    Replies: 2
    Last Post: 05-13-2014, 03:47 PM
  4. Replies: 6
    Last Post: 04-03-2014, 02:02 PM
  5. count (*) timing out
    By aat in forum Queries
    Replies: 2
    Last Post: 12-14-2013, 06:04 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums