Separating out months in a date range

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

Guest

Hi Everyone:

I have a table that shows how long a member has been effective with my
company. There are 2 fields, datefrom and dateto, that show length of time
and I've been using a separate table to break up the date range into
individual months. The table has one field called Midmonth that is a date
for each month with 15 as the day. In my query, my criteria for Midmonth is
"between [datefrom] and [dateto]". However, some of the date ranges don't
cover a full month so I'm missing some members. Here are some examples of
the ones I'm not capturing

DateFrom DateTo
07/01/2006 07/10/2006
06/26/2006 07/05/2006

I'm trying to figure out how I can change the day in these dates so that I
don't miss any months.
I appreciate your help; thanks in advance.

Elisa
 
Makeover said:
I have a table that shows how long a member has been effective with my
company. There are 2 fields, datefrom and dateto, that show length of time
and I've been using a separate table to break up the date range into
individual months. The table has one field called Midmonth that is a date
for each month with 15 as the day. In my query, my criteria for Midmonth is
"between [datefrom] and [dateto]". However, some of the date ranges don't
cover a full month so I'm missing some members. Here are some examples of
the ones I'm not capturing

DateFrom DateTo
07/01/2006 07/10/2006
06/26/2006 07/05/2006

I'm trying to figure out how I can change the day in these dates so that I
don't miss any months.
Click to expand...


First, you need to refine your definition of "month". Your
first example shows that it's clearly less than an entire
month, but that sort of implies that your second example
could be two months??

What result do you want for these examples:
DateFrom DateTo
07/01/2006 07/1/2006
06/14/2006 07/15/2006

There are a several date related functions that might get
the desired result directly without the use of an auxilary
table. For instance,
DateDiff("m", DateTo, DateFrom)+1
will return 1 and 2 for your examples.

OTOH, maybe the number of days would be more useful:
DateDiff("d", DateTo, DateFrom)+1
 
Back
Top