Add the monthly range in each row using the actual start and end d

[Guest]

I want to be able to add the monthly range in Field3(Start) and Field4(End)
using the actual start and end date Ex: Field1 1/7/05 and Field2 3/26/06.
Each row the actual start and end date will repeat in Field1 and Field2.

Field1 Field2 Field3 Field4
1/7/05 3/26/06 1/7/05 1/31/05
1/7/05 3/26/06 2/1/05 2/31/05
1/7/05 3/26/06 3/1/05 3/31/05
…
1/7/05 3/26/06 3/1/06 3/26/06

Thanks a bunch!!!

 

[Jeff Boyce]

First, don't bother storing "calculated" values. You can use the dates in
your first two fields to calculate start-of-month and end-of-month dates in
a query.

Next, I think Access would choke on your date of "2/31/05".

Finally, I don't see the pattern you're using to generate the Field3 and
Field4 values. If you described what you want in words, rather than
data,... For example, what would you tell an assistant to do if s/he was to
use a paper/pencil system?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

I'll simplify it. If I'm giving an actual date range 1/7/2005 to 3/26/06,
how do I create a query to breakdown the range into monthly ranges, similar
to your first part of the response. It would look like this:

1/7/2005 - 1/31/2005
2/1/2005 - 2/31/2005
....
3/1/2006 - 3/26/2006

I want to make sure the day of the month from the actual range is included.

Jeff, I appreciate your prompt response!

 

[Guest]

This is close but not exactly. The start and ending months are not shortened
to the input dates.

Use a table named CountNUM containing number from zero to your maximum spread.

SELECT [Enter start date] AS Start, [Enter end date] AS [End],
DateAdd("m",[CountNUM],CVDate([Enter start date])-Day(CVDate([Enter start
date]))+1) AS [Start Month],
DateAdd("m",1,DateAdd("m",[CountNUM],CVDate([Enter start
date])-Day(CVDate([Enter start date]))+1))-1 AS [End Month]
FROM CountNumber
WHERE (((DateAdd("m",[CountNUM],CVDate([Enter start date])-Day(CVDate([Enter
start date]))+1))<=CVDate([Enter end date])));

 

[Guest]

Works great!! Thanks for the quick response!!

This is close but not exactly. The start and ending months are not shortened
to the input dates.

Use a table named CountNUM containing number from zero to your maximum spread.

SELECT [Enter start date] AS Start, [Enter end date] AS [End],
DateAdd("m",[CountNUM],CVDate([Enter start date])-Day(CVDate([Enter start
date]))+1) AS [Start Month],
DateAdd("m",1,DateAdd("m",[CountNUM],CVDate([Enter start
date])-Day(CVDate([Enter start date]))+1))-1 AS [End Month]
FROM CountNumber
WHERE (((DateAdd("m",[CountNUM],CVDate([Enter start date])-Day(CVDate([Enter
start date]))+1))<=CVDate([Enter end date])));

I want to be able to add the monthly range in Field3(Start) and Field4(End)
using the actual start and end date Ex: Field1 1/7/05 and Field2 3/26/06.
Each row the actual start and end date will repeat in Field1 and Field2.

Field1 Field2 Field3 Field4
1/7/05 3/26/06 1/7/05 1/31/05
1/7/05 3/26/06 2/1/05 2/31/05
1/7/05 3/26/06 3/1/05 3/31/05
…
1/7/05 3/26/06 3/1/06 3/26/06

Thanks a bunch!!!