Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 27th, 2011, 09:00 AM
Registered User
Join Date: Sep 2011
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Talking Generating year range in report

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.

Old October 3rd, 2011, 01:07 AM
Registered User
Join Date: Sep 2011
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Solved this one.

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.

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
    Do While Not rs.EOF
        If range = "" Then
            range = rs!recordyear
            counter = 1
            If rs!recordyear = CInt(range) + counter Then
                counter = counter + 1
                If rs!recordyear <> range Then range = range & "-" & rs!recordyear
                returnRecord = rs.Bookmark
                Do While counter > 0 And Not rs.BOF
                    rs!range = range
                    counter = counter - 1
                range = ""
                rs.Bookmark = returnRecord
            End If
        End If
        If Not rs.EOF Then rs.MoveNext
        If rs.EOF And counter > 0 Then GoTo runDown
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
    MsgBox Err.Description
End Sub
Old October 25th, 2011, 01:02 AM
Friend of Wrox
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts

Thanks for sharing your solution!
Boyd Trimmell aka HiTechCoach (.com)
Microsoft Access MVP Alumni 2010-2015

Similar Threads
Thread Thread Starter Forum Replies Last Post
Minus 1 Day and Year range Wenggo MySQL 2 October 23rd, 2010 11:46 PM
Using month & year variable to get date in range rsearing ASP.NET 2.0 Professional 2 July 5th, 2008 08:30 AM
Month and Year to Date on a Report Mitch Access 2 February 20th, 2007 10:23 AM
Is database necessary for generating report. radhekrishna BOOK: Professional Crystal Reports for VS.NET 0 January 8th, 2007 06:10 AM
Desc sorted monthly report across several year Fehrer Access 11 February 7th, 2004 01:26 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.