Custom Date Partition Function

[cjohns38 via AccessMonster.com]

I need to create a custom date partition function so I can run counts between
the dates the user sets and the number of segments someone sets. Think of it
as a the partition function except with dates. Below is a break down of
things conceptually and then a rough outline on some VBA that tries to do
this stuff………..any help on getting the function to work would be greatly
appreciated.

*************************************************************
Manual Example
*************************************************************

****************
Start Date: 1/1/2009
End Date: 2/1/2009
Segments: 4
****************

****************
Calculate Segment Length and Segments
****************
(EndDate)-(StartDate) = Length of Segment in days
Example: (2/1/2009)-(1/1/2009)=31

Round((Length of Segment in Days) / (Segments)) = Days to add
Example: Round(31/ 4) = 8

Break dates into segments:
Segment1 which is 1/1/2009 = (StartDate)
Segment2 which is 1/9/2009 = (1/1/2009+8)
Segment3 which is 1/17/2009 = (1/9/2009+8)
Segment4 which is 1/25/2009 = (1/17/2009+8)

****************
Example Start Data
****************
ID Date
1 1/1/2009
2 1/8/2009
3 1/16/2009
4 1/24/2009
5 1/25/2009
6 2/1/2009





****************
Intended End Product so I can run counts on Id’s between dates
****************
ID Date DatePartition
1 1/1/2009 1/1/2009
2 1/8/2009 1/1/2009
3 1/16/2009 1/17/2009
4 1/24/2009 1/17/2009
5 1/25/2009 1/25/2009
6 2/1/2009 1/25/2009


****************
Rough Outline Of VBA Code…..doesn’t work but it’s conceptually what I’m
trying to do……
****************

Function DatePartition(StartDate As Date, EndDate As Date, DateVar As Date,
Segments As Integer)
'Calculate the difference in start date and end date in days then divide by
the number of segments
SegmentLength = Round((datediff("d", StartDate, EndDate) / Segments))

'Create temporary segments using the text date seg and the counter i so
dateseg1-datesegn
i = 1
Do Until i = Segments + 1
myvarname = "dateseg" & i 'increment the variable names by i
myvarname = DateAdd("d", SegmentLength * i, StartDate) 'increment the
segment length by i
i = i + 1
Loop

'Loop through temporary segments and place queried dates into segments
x = Segments
Do Until x = 0
If DateVar < "dateseg" & 8 Then 'if the queried date is less than the
datesegment it's in that segment
DatePartition = "dateseg" & x
x = x - 1
Loop
End Function

Any takers on helping me figure this out?

 

[KARL DEWEY]

Create a table named CountNumber with field CountNUM containing numbers from
0 (zero) through your maximum number of segment.

SELECT DateAdd("d",IIf(Round(CVDate([EndDate])-CVDate([StartDate]))\[Enter
number of segments]<Round(CVDate([EndDate])-CVDate([StartDate]))/[Enter
number of segments],Round(CVDate([EndDate])-CVDate([StartDate]))\[Enter
number of segments]+1,Round(CVDate([EndDate])-CVDate([StartDate]))\[Enter
number of segments])*[CountNUM],[StartDate]) AS Expr2, CountNumber.CountNUM
FROM CountNumber
WHERE (((CountNumber.CountNUM)<=[Enter number of segments]));

 

[cjohns38 via AccessMonster.com]

Hi Karl,

A couple points:

1) The query segments date ranges the way I was intending which is great.
Unfortunately, I’m not sure how I apply those groupings to a date variable in
another table. For example, create the table example with the following info.
How do I use the segments created by your query and place the dates in the
example table into those groupings? Normally I would use an IIF statement but
that requires hard coding and the IIF statement changes based on the number
of segments. How do I get around this or can I?

ID Date
1 1/1/2009
2 1/8/2009
3 1/16/2009
4 1/24/2009
5 1/25/2009
6 2/1/2009

2) The final segment may go beyond the end date and I need to default those
instances to the max date. For example, if I put in an end date of 2/1/2010
and a start date of 1/1/2010 and use four segments the last segment date is
2/2/2010 when it should just be the max. Is there a way to compensate for
that in the code?

FYI, all I'm trying to do is create a system where users can pick start dates,
end dates, and the number of segments to generate bar charts. I need the
segment to create the X axis categories and then will use a count for the y
axis. We are basically doing counts of across time (one month, two months,
half a year, whatever) and displaying it on a bar chart......who knew bar
charts could be so challenging.

Thanks again for your help.

 

[KARL DEWEY]

I’m not sure how I apply those groupings to a date variable in another
table.
I do not follow what you want to do. Do you want to append records withe
the dates?

About the last date being beyond EndDate just use a follow-on query with IIF
statement - IIF(NewDate > EndDate, EndDate, NewDate)