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

"navin" <navin.narayana@gmail.com> wrote in message 
news:d8a3e535-1948-4e6c-bdaf-4ff3eb1ea25d@e23g2000prf.googlegroups.com...
> 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

-- 
Brendan Reynolds 

What happens if you try the query you suggested?

You could use DateDiff() if you prefer.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"navin" <navin.narayana@gmail.com> wrote in message
news:d8a3e535-1948-4e6c-bdaf-4ff3eb1ea25d@e23g2000prf.googlegroups.com...
> 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

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

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message
news:ekOj8BPiIHA.5900@TK2MSFTNGP02.phx.gbl...
> What happens if you try the query you suggested?
> 
> You could use DateDiff() if you prefer.
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "navin" <navin.narayana@gmail.com> wrote in message
> news:d8a3e535-1948-4e6c-bdaf-4ff3eb1ea25d@e23g2000prf.googlegroups.com...
>> 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

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/

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"navin" <navin.narayana@gmail.com> wrote in message 
news:d8a3e535-1948-4e6c-bdaf-4ff3eb1ea25d@e23g2000prf.googlegroups.com...
> 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