MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

### Calculate number of days query

• Follow

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

thanks,
navin
```
 0

```"navin" <navin.narayana@gmail.com> wrote in message
> 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.
>
>
> 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

```
 0

```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
> 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.
>
>
> thanks,
> navin
```
 0

```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
>> 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.
>>
>>
>> thanks,
>> navin
```
 0

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

"navin" <navin.narayana@gmail.com> wrote in message
> 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.
>
>
> thanks,
> navin

```
 0