build list of dates as string

[steve-oc]

Hi

I have a table of dates, say:

2009-01-15
2009-01-16
2009-01-17
2009-01-18
2009-01-21
2009-01-22
2009-01-25
2009-01-26
2009-01-27
2009-01-30

I'm trying to write a function that will pull these dates into a rst and then output as:

15-18, 21, 22, 25-27 & 30 January 2009

ie identifying where the consecutive sequences of more than two dates are grouped together.

Has anyone got anything existing that would help me out with this? - am going in circles trying to identify the second and third dates as being in sequence (or not)...

Many thanks
Steve

 

[KenHigg]

I think you are going to have to populate an intermediate type table by stepping through the reords manually and write code that does a comparison from one record to the next.

 

[steve-oc]

Yep, I get that - it's just the comparison isn't just last record, next record. It has to include the next again to see if it's a sequence greater than two (if so start a <first date> hyphen <find last date in sequence>) otherwise separate them by comma, move next...

Is making my head hurt - was hoping someone might have written similar or seen a solution elsewhere.

Thanks

 

[KenHigg]

So as you step through the records, the last thing you do before you move to the next record in save the current value in a variable. Then when you go to the next record you can compare it's value to the previous one.

 

[Darth Vodka]

aaaah...but what if the years and months differ?

and would it not be

15-18, 21-22, 25-27 & 30 January 2009

not

15-18, 21, 22, 25-27 & 30 January 2009

 

[Darth Vodka]

how's this for a bored five miutes?

'---------------------------------------------------------------------------------------
' Procedure : OrderlyDates
' Author    : Darth Vodka
' Date      : 13/01/09
'---------------------------------------------------------------------------------------
'
Function OrderlyDates(strTableQuery As String, strDateField As String) As String
    Dim db                      As DAO.Database
    Dim rs                      As DAO.Recordset
    Dim dtStartList             As Date
    Dim dtLoopDatePrevious      As Date
    Dim booNewBit               As Boolean
'
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("select * from " & strTableQuery & " order by " & strDateField, dbReadOnly)
    
    rs.MoveFirst
    dtLoopDatePrevious = rs(strDateField)
    OrderlyDates = ""
    
    rs.MoveNext
    
    Do Until rs.EOF
        If rs(strDateField) - dtLoopDatePrevious = 1 And OrderlyDates <> "" Then
            'consecutive day
            booNewBit = False
        Else
            booNewBit = True
        End If
        If booNewBit Then
            OrderlyDates = OrderlyDates & Day(dtLoopDatePrevious) & "," & Day(rs(strDateField)) & "-"
        End If
        dtLoopDatePrevious = rs(strDateField)
        rs.MoveNext
    Loop
    
    If Right(OrderlyDates, 1) = "-" Then
        'get rid of ending -
        OrderlyDates = Left(OrderlyDates, Len(OrderlyDates) - 1)
    End If
    
    'stick month and year on
    OrderlyDates = OrderlyDates & Format(dtLoopDatePrevious, " MMMM YYYY")
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing


End Function

 

[Darth Vodka]

you can probably do it in SQL with a lot of grouping, MAX, MIN...if MAX=MIN, MAX ELSE MIN + "-" + MAX

blah blah