Generating year range in report

jixig · September 27th, 2011, 09:00 AM

Hi folks,

I need to generate a report that would give me year ranges instead of just a series of numbers. For simplified purposes, let's assume my table just consists of ID, a first name, and a year.. So maybe..

1 John 1980
2 John 1981
3 John 1982
4 John 1985
5 Melissa 1980
6 Steve 1980

And I need... John: 1980-1982, 1985
Melissa: 1980
Steve: 1980

Any ideas? I was thinking about running some kind of VBA maintenance to update an undisplayed extra text field on the table to maintain the range, then doing group bys to display the information. But I was hoping that there was a better way to go about it. I was planning to store the year as an integer value, since month and day will never come into play.

Thanks,
--Josh

jixig · October 3rd, 2011, 01:07 AM

Hi folks,

If anyone is curious I solved this for myself basically using the idea I had previously.. Here's what I came up with. recordYear is int, range is string. It's contingent on fields not being null or duplicated, I believe. In my report I'll do group bys to display data correctly.

Code:

Private Sub Command0_Click()
    On Error GoTo errMsg
    Dim db As Database, rs As DAO.Recordset
    Dim range As String, counter As Integer
    Dim returnRecord As Variant
    
    range = ""
    counter = 0
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT * from t_Entries ORDER BY recordYear")
    If rs.BOF And rs.EOF Then Exit Sub
    rs.MoveFirst
    Do While Not rs.EOF
        If range = "" Then
            range = rs!recordyear
            counter = 1
        Else
            If rs!recordyear = CInt(range) + counter Then
                counter = counter + 1
            Else
runDown:
                rs.MovePrevious
                If rs!recordyear <> range Then range = range & "-" & rs!recordyear
                returnRecord = rs.Bookmark
                Do While counter > 0 And Not rs.BOF
                    rs.Edit
                    rs!range = range
                    rs.Update
                    counter = counter - 1
                    rs.MovePrevious
                Loop
                range = ""
                rs.Bookmark = returnRecord
            End If
        End If
        If Not rs.EOF Then rs.MoveNext
        If rs.EOF And counter > 0 Then GoTo runDown
    Loop
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
errMsg:
    MsgBox Err.Description
End Sub

HiTechCoach · October 25th, 2011, 01:02 AM

Thanks for sharing your solution!