Results 1 to 6 of 6
  1. #1
    Khalil Handal is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    82

    Date Calculations in a query

    Hi,

    A table has two date fields DateBorrowed, DateReturned
    I am building a query and need the following:
    1- A calculated field: DueDate = DateBorrowed+ 21 days


    2- A Calculated field: DaysLate = DateReturned - DueDate
    The aim of the criteria is find DaysLate that is greater than 21 days.

    I am struggling with it. Any help is appreciated

    Khalil

  2. #2
    mike60smart is online now Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    283
    Hi Khalil

    1- A calculated field: DueDate = DateBorrowed+ 21 days - Use DateAdd in query (Due Date: DateAdd("d",21,[DateBorrowed])
    2- A Calculated field: DaysLate = DateReturned - DueDate - Use DateDiff in query (DaysLate: DateDiff("d",[Datereturned],[DueDate])

  3. #3
    mike60smart is online now Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    283
    Note there should not be a space after the Colon

  4. #4
    Khalil Handal is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    82
    Thanks,
    The first one works well for Due Date. (Due Date: DateAdd("d",21,[DateBorrowed])

    The second one for Days Late does not work if DateReturned is Null or it is not specified yet.

    How can we say: DaysLate = Todays date - DateBorrowed - 21 days ?
    Because DateReturned should be 21 days after DateBorrowed.

  5. #5
    mike60smart is online now Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    283
    Hi

    You can use the following to indicate it is Overdue:-

    Due Return:IIf(DateDiff("d",[DateBorrowed],Date())>=21,"Overdue","")

  6. #6
    Khalil Handal is offline Advanced Beginner
    Windows 10 Access 2003
    Join Date
    Jun 2016
    Posts
    82
    Hi again,

    Due Return:IIf(DateDiff("d",[DateBorrowed],Date())>=21,"Overdue","")

    Your solution works fine and shows all the records with DateReturned Is Null and have value of "overdue" and the rest of the values are "" as expected.

    I tried this one that gives only the "overdue" records.
    Here it is:
    DaysLate: Date()-[DateBorrowed]-21 It is used with criteria >0.

    It works fine.
    Is this the correct way of using it?

    Khalil

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

Similar Threads

  1. Future date calculations
    By ACu in forum Access
    Replies: 2
    Last Post: 05-02-2019, 09:12 PM
  2. calculations on date
    By harrie in forum Access
    Replies: 3
    Last Post: 12-06-2016, 10:59 PM
  3. Replies: 7
    Last Post: 09-12-2011, 12:03 PM
  4. Date Calculations
    By Polarbilly in forum Forms
    Replies: 2
    Last Post: 06-01-2011, 12:55 PM
  5. Date calculations
    By NOTLguy in forum Access
    Replies: 10
    Last Post: 10-09-2010, 06:41 AM

Tags for this Thread

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 - Senior Forums