# Date Calculations in a query

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. 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. 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

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. 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","")

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