Results 1 to 4 of 4

Difference of date in the same field

  1. #1
    LOUIX is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Feb 2018
    Posts
    7

    Difference of date in the same field

    Dear All,
    As per the attachement, I would like to know if we can get the difference of date if it in the same field
    Unfortunately, the date is not in 2 fields,
    Is it possible to know the difference of date in the same field
    Thanks
    Ludovic
    Attached Files Attached Files

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,339
    You need to get value from another record of same table? Review http://allenbrowne.com/subquery-01.html#AnotherRecord
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

  3. #3
    LOUIX is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Feb 2018
    Posts
    7
    Thanks June7,
    But this is really too complex for me to understand, unless you can do it in the attachment for me just to help me
    LR

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    44,339
    It was too complex for me when I first discovered but I spent time studying it and finally figured it out and believe me, it took more than an hour. Did you really make any effort?

    But guess I can show off:

    Try:

    SELECT tblBDOrder.*, dtOrder - (SELECT TOP 1 Dupe.dtOrder FROM tblBDOrder AS Dupe WHERE Dupe.dtOrder<tblBDOrder.dtOrder ORDER BY Dupe.dtOrder DESC) AS ElapseDays
    FROM tblBDOrder ORDER BY dtOrder;

    Now if you want to know how many days between orders for the same customer:

    SELECT tblBDOrder.*, dtOrder - (SELECT TOP 1 Dupe.dtOrder FROM tblBDOrder AS Dupe WHERE Dupe.dtOrder<tblBDOrder.dtOrder AND Dupe.txtCustomer = tblBDOrder.txtCustomer ORDER BY Dupe.dtOrder DESC) AS ElapseDays
    FROM tblBDOrder ORDER BY txtCustomer, dtOrder;
    To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression. Attachment Manager is below Advanced editor window, click Go Advanced below Quick Reply window.

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

Similar Threads

  1. Date difference of every 2nd record in a field
    By UnlucksMcGee in forum Access
    Replies: 7
    Last Post: 01-06-2018, 09:33 PM
  2. Replies: 3
    Last Post: 03-02-2016, 08:39 AM
  3. Difference between the value from two different date
    By mahmud1180 in forum Programming
    Replies: 2
    Last Post: 10-02-2014, 07:40 AM
  4. Date Difference Not Quite Right
    By StevenCV in forum Access
    Replies: 11
    Last Post: 01-30-2014, 07:12 AM
  5. Replies: 1
    Last Post: 02-12-2013, 02:48 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
  •  
Tech Forums: Microsoft Office Forums