How to calculate the nr of days??

[Linda]

I have the following system.
Every 2 months we read in invoices that we import into an
access table. For example for the month of March we
needed to find out how many days there are in March. That
was simple by using DateSerial : Day(DateSerial(Year
([InvoiceDate]),Month([InvoiceDate]),0))This returns 31.
Now the thing is that I have a column where EndDates are
stored in and whenever for example there are enddates
that are less then the end of the Invoice Month (March)
the difference of the nr of days need to be calculated.
Let's say that there is an enddate that is = #03/15/2004#
then the difference should be 31 - #03/15/2004# which
should be 16. Is there a way to calculate these
differences using a Select Query? TIA.

 

[Guest]

Linda -

You can use the Datediff function to accomplish this.... DateDiff(interval, date1,date2) ... returns date2 - date1 in the interval format (ie, d = days

----- Linda wrote: ----

I have the following system
Every 2 months we read in invoices that we import into an
access table. For example for the month of March we
needed to find out how many days there are in March. That
was simple by using DateSerial : Day(DateSerial(Yea
([InvoiceDate]),Month([InvoiceDate]),0))This returns 31.
Now the thing is that I have a column where EndDates are
stored in and whenever for example there are enddates
that are less then the end of the Invoice Month (March)
the difference of the nr of days need to be calculated.
Let's say that there is an enddate that is = #03/15/2004#
then the difference should be 31 - #03/15/2004# which
should be 16. Is there a way to calculate these
differences using a Select Query? TIA

 

[Linda]

I cannot use DateDiff since I'm comparing a number in
this case 31 (nr of days in March) which I need to
subtract from the enddate #03/15/2004#. The difference
should be 16.

-----Original Message-----
Linda --

You can use the Datediff function to accomplish this....

DateDiff(interval, date1,date2) ... returns date2 - date1
in the interval format (ie, d = days)

----- Linda wrote: -----

I have the following system.
Every 2 months we read in invoices that we import into an
access table. For example for the month of March we
needed to find out how many days there are in March. That
was simple by using DateSerial : Day(DateSerial(Year
([InvoiceDate]),Month([InvoiceDate]),0))This returns 31.
Now the thing is that I have a column where EndDates are
stored in and whenever for example there are enddates
that are less then the end of the Invoice Month (March)
the difference of the nr of days need to be calculated.
Let's say that there is an enddate that is = #03/15/2004#
then the difference should be 31 - #03/15/2004# which
should be 16. Is there a way to calculate these
differences using a Select Query? TIA.

.

 

[Guest]

Linda -

I misread what you were trying to do....so here is an approach to conside

you could use the Day function on your date ... this will return a the Day part of your date then subtract that from your number of days... see belo

31 - day("3/15/2004") will give you 16 ... note the quotations ... so treat your date as a string value from the control that contains it....this should give you what you are looking for...

----- Linda wrote: ----

I cannot use DateDiff since I'm comparing a number in
this case 31 (nr of days in March) which I need to
subtract from the enddate #03/15/2004#. The difference
should be 16

-----Original Message----
Linda -

DateDiff(interval, date1,date2) ... returns date2 - date1
in the interval format (ie, d = days

----- Linda wrote: ----
I have the following system

Every 2 months we read in invoices that we import into an
access table. For example for the month of March we
needed to find out how many days there are in March. That
was simple by using DateSerial : Day(DateSerial(Yea
([InvoiceDate]),Month([InvoiceDate]),0))This returns 31.
Now the thing is that I have a column where EndDates are
stored in and whenever for example there are enddates
that are less then the end of the Invoice Month (March)
the difference of the nr of days need to be calculated.
Let's say that there is an enddate that is = #03/15/2004#
then the difference should be 31 - #03/15/2004# which
should be 16. Is there a way to calculate these
differences using a Select Query? TIA