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.
"Lunch" wrote:
> 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?
>
Similar Threads
