no. of days and month

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

in my Query i have the following fields dateofS,stationName,PetrolSales...
dateofS field have date values like 02/09/2005, 03/09/2005.....etc.

in my report and chart (yearly report and monthly report) i have to display

no.of months and also days. exactly.(because some time data not available
for all dates in that month) kindly please help me..in this issue.


Thanks
 
Have you looked at the Totals query in Access HELP? From your description,
it sounds like that would be an approach.
 
sorry i couldnt get help...


you see

in my table for dateofs field

dateofS

09/10/2005
09/10/2005
09/10/2005
10/10/2005
10/10/2005
10/10/2005
11/10/2005
11/10/2005
.......
.......
how can i display in my report no. of month and no.of days.

(between the first and last dates) kindly help me.

Thanks
 
I am confused. If I have a table with a date/time field containing values
like those in your example, I would use a Totals query to count the number
of times each date occurred.

What happens when you try a totals query? Can you post the SQL statement of
the Totals query you are using?
 
could you please tel me totals query....?
and kindly note that i have to find min date and max date in that coulmn
and have to find difference between these two dates and display
like 3 months and 13 days.

kindly reply.
 
Take a look in Access HELP about Totals queries, which also help you do
minimum and maximum.

Jeff Boyce
<Office/Access MVP>
 
Dear is:

The value you seem to be requesting could be added to your query by adding a
column. This value would show up in every row of the subsequent query
results, and would be the same value in every row.

I recommend using a subquery such as this:

(SELECT MAX(dateofS)
FROM YourTable) -
(SELECT MIN(dateofS)
FROM YourTable)
AS DateDifference

If you have difficulty adding this to your query, please post the rest of
the query you have and I'll try to add it.

Tom Ellison
 
one of my query is like this..i paste the sql



SELECT DISTINCTROW Format$(details.dateofS,'mm yyyy') AS [dateofS By Month],
details.stid, Sum(details.petrol) AS [Sum Of petrol], (select max(dateofS)
from details)-(select min(dateofs) from details ) AS NOOFDAYS
FROM details
GROUP BY Format$(details.dateofS,'mm yyyy'), details.stid
HAVING (((details.stid)=[Forms]![searchfrm]![stidcmb]));


here NOOFDAYS GIVES ME 314 days but my boss ask me to display..
10 month and 14 days ....in the report header....
 
Try the "More Complete DateDiff Function" Graham Seach and Doug Steele
wrote.

http://www.accessmvp.com/djsteele/Diff2Dates.html

You specify how you want the difference between two date/times to be
calculated by providing which of ymdhns (for years, months, days, hours,
minutes and seconds) you want calculated.

You can make 314 days return 10 months and 14 days IF you decide that every
month is 30 days long.
314\30 & " Months" & 314 mod 30 & " days"

ismail said:
one of my query is like this..i paste the sql



SELECT DISTINCTROW Format$(details.dateofS,'mm yyyy') AS [dateofS By
Month],
details.stid, Sum(details.petrol) AS [Sum Of petrol], (select max(dateofS)
from details)-(select min(dateofs) from details ) AS NOOFDAYS
FROM details
GROUP BY Format$(details.dateofS,'mm yyyy'), details.stid
HAVING (((details.stid)=[Forms]![searchfrm]![stidcmb]));


here NOOFDAYS GIVES ME 314 days but my boss ask me to display..
10 month and 14 days ....in the report header....



Tom Ellison said:
Dear is:

The value you seem to be requesting could be added to your query by
adding a
column. This value would show up in every row of the subsequent query
results, and would be the same value in every row.

I recommend using a subquery such as this:

(SELECT MAX(dateofS)
FROM YourTable) -
(SELECT MIN(dateofS)
FROM YourTable)
AS DateDifference

If you have difficulty adding this to your query, please post the rest of
the query you have and I'll try to add it.

Tom Ellison
Click to expand...
Click to expand...
 
Hi,



SELECT Month(theDate), COUNT(*)
FROM ( SELECT DISTINCT dateOfS As theDate FROM myTableNameHere)
GROUP BY Month(theDate)


should do.




Hoping it may help,
Vanderghast, Access MVP
 
NOOFDAYS: Diff2Dates("md",(select min(dateofs) from details),(select
max(dateofs) from details),True)

Gives me the result . for ex. 10 months 8 days
but i have 10 month 12 days and in the first month i miss 4 days.

is it possible to rectify.

John Spencer said:
Try the "More Complete DateDiff Function" Graham Seach and Doug Steele
wrote.

http://www.accessmvp.com/djsteele/Diff2Dates.html

You specify how you want the difference between two date/times to be
calculated by providing which of ymdhns (for years, months, days, hours,
minutes and seconds) you want calculated.

You can make 314 days return 10 months and 14 days IF you decide that every
month is 30 days long.
314\30 & " Months" & 314 mod 30 & " days"

ismail said:
one of my query is like this..i paste the sql



SELECT DISTINCTROW Format$(details.dateofS,'mm yyyy') AS [dateofS By
Month],
details.stid, Sum(details.petrol) AS [Sum Of petrol], (select max(dateofS)
from details)-(select min(dateofs) from details ) AS NOOFDAYS
FROM details
GROUP BY Format$(details.dateofS,'mm yyyy'), details.stid
HAVING (((details.stid)=[Forms]![searchfrm]![stidcmb]));


here NOOFDAYS GIVES ME 314 days but my boss ask me to display..
10 month and 14 days ....in the report header....



Tom Ellison said:
Dear is:

The value you seem to be requesting could be added to your query by
adding a
column. This value would show up in every row of the subsequent query
results, and would be the same value in every row.

I recommend using a subquery such as this:

(SELECT MAX(dateofS)
FROM YourTable) -
(SELECT MIN(dateofS)
FROM YourTable)
AS DateDifference

If you have difficulty adding this to your query, please post the rest of
the query you have and I'll try to add it.

Tom Ellison


in my Query i have the following fields
dateofS,stationName,PetrolSales...
dateofS field have date values like 02/09/2005, 03/09/2005.....etc.

in my report and chart (yearly report and monthly report) i have to
display

no.of months and also days. exactly.(because some time data not
available
for all dates in that month) kindly please help me..in this issue.


Thanks
Click to expand...
Click to expand...
Click to expand...
 
Well, as far as I know the function is accurate.

It counts the number of months and the number of day. 312 days is not going
to be 10 months and 12 days. It is going to be 10 months and some number of
days less than 12 (some months have 31 days in them).

If you want the calculation to be based on all months having 30 days, then
you will have to come up with another way of doing this.

312 days divide by 30 is 10 months with 12 days left over.

Can you give a specific set of dates and what you specifically want to see?


ismail said:
NOOFDAYS: Diff2Dates("md",(select min(dateofs) from details),(select
max(dateofs) from details),True)

Gives me the result . for ex. 10 months 8 days
but i have 10 month 12 days and in the first month i miss 4 days.

is it possible to rectify.

John Spencer said:
Try the "More Complete DateDiff Function" Graham Seach and Doug Steele
wrote.

http://www.accessmvp.com/djsteele/Diff2Dates.html

You specify how you want the difference between two date/times to be
calculated by providing which of ymdhns (for years, months, days, hours,
minutes and seconds) you want calculated.

You can make 314 days return 10 months and 14 days IF you decide that
every
month is 30 days long.
314\30 & " Months" & 314 mod 30 & " days"

ismail said:
one of my query is like this..i paste the sql



SELECT DISTINCTROW Format$(details.dateofS,'mm yyyy') AS [dateofS By
Month],
details.stid, Sum(details.petrol) AS [Sum Of petrol], (select
max(dateofS)
from details)-(select min(dateofs) from details ) AS NOOFDAYS
FROM details
GROUP BY Format$(details.dateofS,'mm yyyy'), details.stid
HAVING (((details.stid)=[Forms]![searchfrm]![stidcmb]));


here NOOFDAYS GIVES ME 314 days but my boss ask me to display..
10 month and 14 days ....in the report header....



:

Dear is:

The value you seem to be requesting could be added to your query by
adding a
column. This value would show up in every row of the subsequent query
results, and would be the same value in every row.

I recommend using a subquery such as this:

(SELECT MAX(dateofS)
FROM YourTable) -
(SELECT MIN(dateofS)
FROM YourTable)
AS DateDifference

If you have difficulty adding this to your query, please post the rest
of
the query you have and I'll try to add it.

Tom Ellison


in my Query i have the following fields
dateofS,stationName,PetrolSales...
dateofS field have date values like 02/09/2005, 03/09/2005.....etc.

in my report and chart (yearly report and monthly report) i have to
display

no.of months and also days. exactly.(because some time data not
available
for all dates in that month) kindly please help me..in this issue.


Thanks
Click to expand...
Click to expand...
Click to expand...
 
kindly look into this matter

m: Diff2Dates("ymd",(select min(dateofs) from details where
year(dateofS)=Forms![searchfrm]![Combo30] ),(select max(dateofs) from details
where year(dateofS)=Forms![searchfrm]![Combo30]),True)


This is is my query and in my table i have full year from 01/01/2005 to
31/12/2005
ie.365 days...

in this case i like to see in my report 12 months or something else
but this query gives 11 months 30 days....anything missing
i use this above query as subquery.

Thanks


John Spencer said:
Well, as far as I know the function is accurate.

It counts the number of months and the number of day. 312 days is not going
to be 10 months and 12 days. It is going to be 10 months and some number of
days less than 12 (some months have 31 days in them).

If you want the calculation to be based on all months having 30 days, then
you will have to come up with another way of doing this.

312 days divide by 30 is 10 months with 12 days left over.

Can you give a specific set of dates and what you specifically want to see?


ismail said:
NOOFDAYS: Diff2Dates("md",(select min(dateofs) from details),(select
max(dateofs) from details),True)

Gives me the result . for ex. 10 months 8 days
but i have 10 month 12 days and in the first month i miss 4 days.

is it possible to rectify.

John Spencer said:
Try the "More Complete DateDiff Function" Graham Seach and Doug Steele
wrote.

http://www.accessmvp.com/djsteele/Diff2Dates.html

You specify how you want the difference between two date/times to be
calculated by providing which of ymdhns (for years, months, days, hours,
minutes and seconds) you want calculated.

You can make 314 days return 10 months and 14 days IF you decide that
every
month is 30 days long.
314\30 & " Months" & 314 mod 30 & " days"

one of my query is like this..i paste the sql



SELECT DISTINCTROW Format$(details.dateofS,'mm yyyy') AS [dateofS By
Month],
details.stid, Sum(details.petrol) AS [Sum Of petrol], (select
max(dateofS)
from details)-(select min(dateofs) from details ) AS NOOFDAYS
FROM details
GROUP BY Format$(details.dateofS,'mm yyyy'), details.stid
HAVING (((details.stid)=[Forms]![searchfrm]![stidcmb]));


here NOOFDAYS GIVES ME 314 days but my boss ask me to display..
10 month and 14 days ....in the report header....



:

Dear is:

The value you seem to be requesting could be added to your query by
adding a
column. This value would show up in every row of the subsequent query
results, and would be the same value in every row.

I recommend using a subquery such as this:

(SELECT MAX(dateofS)
FROM YourTable) -
(SELECT MIN(dateofS)
FROM YourTable)
AS DateDifference

If you have difficulty adding this to your query, please post the rest
of
the query you have and I'll try to add it.

Tom Ellison


in my Query i have the following fields
dateofS,stationName,PetrolSales...
dateofS field have date values like 02/09/2005, 03/09/2005.....etc.

in my report and chart (yearly report and monthly report) i have to
display

no.of months and also days. exactly.(because some time data not
available
for all dates in that month) kindly please help me..in this issue.


Thanks
Click to expand...
Click to expand...
Click to expand...
 
One, I didn't write the code so if you want it "corrected" I suggest you ask
the authors or do your own custom modification.
Two, It works as expected for me. Since I get 364 days not 365 between
1/1/05 and 31/12/05. The DateDiff function and the Diff2Dates function both
count the number of boundaries crossed. For example, Jan 1 2005 to Jan 1
2005 is ZERO days. Jan 1 to Jan 2 is ONE day.

You can probably fix your situation by adding one day to your calculated
maximum date.

m: Diff2Dates("ymd",(select min(dateofs) from details where
year(dateofS)=Forms![searchfrm]![Combo30] ),DateAdd("d",1,(select
max(dateofs) from details
where year(dateofS)=Forms![searchfrm]![Combo30])),True)

ismail said:
kindly look into this matter

m: Diff2Dates("ymd",(select min(dateofs) from details where
year(dateofS)=Forms![searchfrm]![Combo30] ),(select max(dateofs) from
details
where year(dateofS)=Forms![searchfrm]![Combo30]),True)


This is is my query and in my table i have full year from 01/01/2005 to
31/12/2005
ie.365 days...

in this case i like to see in my report 12 months or something else
but this query gives 11 months 30 days....anything missing
i use this above query as subquery.

Thanks


John Spencer said:
Well, as far as I know the function is accurate.

It counts the number of months and the number of day. 312 days is not
going
to be 10 months and 12 days. It is going to be 10 months and some number
of
days less than 12 (some months have 31 days in them).

If you want the calculation to be based on all months having 30 days,
then
you will have to come up with another way of doing this.

312 days divide by 30 is 10 months with 12 days left over.

Can you give a specific set of dates and what you specifically want to
see?


ismail said:
NOOFDAYS: Diff2Dates("md",(select min(dateofs) from details),(select
max(dateofs) from details),True)

Gives me the result . for ex. 10 months 8 days
but i have 10 month 12 days and in the first month i miss 4 days.

is it possible to rectify.

:

Try the "More Complete DateDiff Function" Graham Seach and Doug Steele
wrote.

http://www.accessmvp.com/djsteele/Diff2Dates.html

You specify how you want the difference between two date/times to be
calculated by providing which of ymdhns (for years, months, days,
hours,
minutes and seconds) you want calculated.

You can make 314 days return 10 months and 14 days IF you decide that
every
month is 30 days long.
314\30 & " Months" & 314 mod 30 & " days"

one of my query is like this..i paste the sql



SELECT DISTINCTROW Format$(details.dateofS,'mm yyyy') AS [dateofS By
Month],
details.stid, Sum(details.petrol) AS [Sum Of petrol], (select
max(dateofS)
from details)-(select min(dateofs) from details ) AS NOOFDAYS
FROM details
GROUP BY Format$(details.dateofS,'mm yyyy'), details.stid
HAVING (((details.stid)=[Forms]![searchfrm]![stidcmb]));


here NOOFDAYS GIVES ME 314 days but my boss ask me to display..
10 month and 14 days ....in the report header....



:

Dear is:

The value you seem to be requesting could be added to your query by
adding a
column. This value would show up in every row of the subsequent
query
results, and would be the same value in every row.

I recommend using a subquery such as this:

(SELECT MAX(dateofS)
FROM YourTable) -
(SELECT MIN(dateofS)
FROM YourTable)
AS DateDifference

If you have difficulty adding this to your query, please post the
rest
of
the query you have and I'll try to add it.

Tom Ellison


in my Query i have the following fields
dateofS,stationName,PetrolSales...
dateofS field have date values like 02/09/2005,
03/09/2005.....etc.

in my report and chart (yearly report and monthly report) i have
to
display

no.of months and also days. exactly.(because some time data not
available
for all dates in that month) kindly please help me..in this
issue.


Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top