Calculate number of days query

[navin]

Hi,

In a table i have field "promise date" which tells my suppliers, when
the orders are due to arrrive in the plant. i need to write a query
which will calculate the number of days the supplier is late from the
promise date.

something like: current date - promise date

below is the query in which i want to add the days late number:

SELECT supplier, ordernumber, promiseddate, currentdate-promisedate as
Ageing FROM tblPastDue;

what i need from this query is to also calcuate the number of days
which supplier is late on the order.

please help and let me know, how can i calculate the days in a query

thanks,
navin

 

[Brendan Reynolds]

Hi,

In a table i have field "promise date" which tells my suppliers, when
the orders are due to arrrive in the plant. i need to write a query
which will calculate the number of days the supplier is late from the
promise date.

something like: current date - promise date

below is the query in which i want to add the days late number:

SELECT supplier, ordernumber, promiseddate, currentdate-promisedate as
Ageing FROM tblPastDue;

what i need from this query is to also calcuate the number of days
which supplier is late on the order.

please help and let me know, how can i calculate the days in a query

thanks,
navin

The Date() function returns the current date, so just substitute "Date()"
for "currentdate" in your example ...

Date()-[promisedate] AS Ageing

When you want to simply get a difference in days like this, you can just
subtract one date from another, but you can do the same thing and much more
using the DateDiff() function. Here's an example using the DateDiff()
function that would return the same result as the example above ...

DateDiff("d",[promisedate],Date()) AS Ageing

 

[Allen Browne]

What happens if you try the query you suggested?

You could use DateDiff() if you prefer.

 

[Allen Browne]

Apologies: you will need to use Date() in place of your 'currentdate'.

 

[Douglas J. Steele]

Take a look at the DateDiff function in the Help file.

If you want to ignore weekends (and holidays), take a look in the Date/Time
section of "The Access Web": there are a couple of solutions there
http://www.mvps.org/access/