Custom Date Partition Function

[Lunch]

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?

 

[Daniel Pineault]

To calculate your 'Length of Segment in days' simply use the DateDIff().

What is your 'Segments'? I am confused by it, what does it represent?
--
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.