work out days in each month

[Guest]

Hi.
I have two fields, a start date and an end date. Could you please tell me
how I can work out how many days in each month based on the start date and
end date.
e.g if the start date is 4th Jan 2005 and the end date is 12th July 2004. I
need to know Jan - x days, feb x days, mar x days etc to july - 12 days.

can you help

 

[Guest]

It should state 12th July 2005 (not 2004) sorry.

 

[cpnet]

Are you writing some code to do this, or are you hoping that a database
query can give you this answer? Getting the total days between 2 dates is
easy (see the DateDiff function). However, it'll get pretty complex if you
need to know for each month involved in the date range, how many days there
are.

Can the start and end date occur in a different year (ie Jan 01 2005 - Mar
31 2006)? In that case, you have to include 'slots' to hold days for Jan
2005, and Jan 2006, Feb 2005 and Feb 2006, etc. Or, does the range always
'touch' 7 months (Jan - July of the same year), or could it vary? If it
could vary, I don't think you'll be able to do this with a query. You'll
need to write some code that outputs this info.

 

[Graham R Seach]

Kevin,

If you want to know how many days there are in a particular month, you don't
need the end date, you just need the following formula:
intDaysInMonth = Day(DateSerial(Year(dteMyDate), Month(dteMyDate) + 1,
0))

If you just want to know how many days there are between two dates, then use
the DateDiff() function:
intDaysBetweenDates = DateDiff("d", dteStartDate, dteEndDate)
....or simpler...
intDaysBetweenDates = dteEndDate - dteStartDate

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia