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.

Thanks,
--Josh
 
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.

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
 
Old October 25th, 2011, 01:02 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 245
Thanks: 5
Thanked 24 Times in 23 Posts
Default

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.