Results 1 to 6 of 6
  1. #1
    kanchanj88 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Posts
    4

    To display the correct Date from the given data

    Hi,



    My data contains 3 columns for start date, end date and production.

    The date formats are mm/dd/yyyy hh:mm

    Our fiscal days are between 6:00am to 6:00am i.e. production made during Jan 1, 2015 6:00am (Start time) to Jan 2, 2015 6:00am(End time) is recorded as production made for Jan 1 2015. If end time was Jan 1, 2015 6:00am then that would be recorded as production for Dec 31, 2014.

    Sample Data
    Start_Time_Full End_Time_Full Production needed Answer
    5/30/2015 6:39 5/30/2015 6:45 6 5/30/2015
    8/18/2015 14:38 8/18/2015 14:42 4 8/18/2015
    9/11/2015 2:03 9/11/2015 2:07 4 9/10/2015
    9/11/2015 16:50 9/11/2015 16:58 8 9/11/2015
    9/14/2015 5:45 9/14/2015 6:00 2 9/13/2015
    9/14/2015 14:19 9/14/2015 14:26 7 9/14/2015

    I want my query to just return me the fiscal date in a column; but that date has to meet the criteria mentioned above.

    Thank you in advance for your help!!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Use IIf() expression that tests if the time is before 0600 and if it is, subtract 1 from the date.

    FD: Format([End_Time_Full] - IIf(Format([End_Time_Full], "hhnnss")<="060000", 1, 0), "MM/DD/YYYY")

    Might be an issue if time is exactly midnight because midnight time shows as 000000. Probably don't want to subtract 1 day for this. May need another IIf condition.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    try subtracting 6 hours and 1 second to get the right day - 6 x 60mins x 60 seconds=21600+1=21601

    needed answer=dateadd("s",-21601,[end time full])

  4. #4
    kanchanj88 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Posts
    4
    Both your answers worked guys. Awesome! Thank you!!

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Great, much simpler. And I just figured out that midnight of a day is actually 0 time of the next day. So exactly midnight of 11/15/2015 actually records as 11/16/2015. Which means my expression also deals correctly with midnight.

    Demonstrated with:

    DateAdd("s", 1, #11/15/2015 11:59:59 PM#)

    However, if you want only the date part, not time, use the Format function.

    If you need to do additional calcs with formatted value, will need to convert back to a true date because Format() results in a text string.

    CDate(Format(DateAdd("s", -21601, [End_Time_Full], mm/dd/yyyy"))
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    kanchanj88 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2015
    Posts
    4
    Yes, that's the same expression I used. A combination of Ajax's with the Format statement.
    However I did not know that would convet it to text. This is great. Thank you very much!!

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

Similar Threads

  1. Replies: 11
    Last Post: 07-20-2014, 06:22 PM
  2. Replies: 4
    Last Post: 01-11-2014, 11:57 PM
  3. Display correct formatting on form.
    By chrisfl in forum Forms
    Replies: 6
    Last Post: 05-23-2013, 08:20 AM
  4. Replies: 2
    Last Post: 01-17-2013, 01:25 AM
  5. Replies: 7
    Last Post: 12-06-2012, 03:10 PM

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