Grouping numbers

I have a table which lists player names, teams played for and the
years they played there and my code looks like this
>
SELECT AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
AlsoPlayedFor.TeamName, Min([AlsoPlayedFor].[Year]) & "-" &
Max([AlsoPlayedFor].[Year]) AS [Year]
FROM AlsoPlayedFor
GROUP BY AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
AlsoPlayedFor.TeamName;
>
which takes the Min year and the Max Year and displays it like "Year-
Year"
>
But lets say for example the player played for 5 years so it 1990,
1991, 1992, 1993, 1995
>
It would display as 1990-1995 but I want it to display as 1990-1993,
1995, is this possiable??? Also I need it to gothe other wayso if the
years are 1990, 1992, 1993, 1994, 1995 I want that to display as 1990,
1992-1995.
>
PLEASE HELP

On Mar 3, 11:54 pm, "Chris" <chrislab...@gmail.comwrote:
>
>
>
>
>>>>>>>
Hi, Chris. I see you're still struggling with this problem. Give this
a try:
>
1. Paste the following code into a new Module in your Access database:
>
Public Function ListYears(playerID, teamID)
Dim _
rst As DAO.Recordset, _
prevYear As Integer, _
seqStart As Integer, _
strList As String
>
Set rst = CurrentDb.OpenRecordset( _
"SELECT [Year] FROM [AlsoPlayedFor] " & _
"WHERE [playerID]=" & playerID & " " & _
"AND [teamID]=" & teamID & " " & _
"ORDER BY [Year]", _
dbOpenSnapshot)
strList = ""
prevYear = 0
seqStart = 0
Do While Not rst.EOF
If rst!Year <prevYear + 1 Then
strList = strList & _
IIf(seqStart <prevYear, "-" & prevYear, "") & _
", " & _
rst!Year
seqStart = rst!Year
End If
prevYear = rst!Year
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
ListYears = Mid(strList, 3)
End Function
>
2. Change your query to
>
SELECT AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,
ListYears([playerID],[teamID]) AS Years
FROM AlsoPlayedFor
GROUP BY AlsoPlayedFor.playerID, AlsoPlayedFor.teamID;
>
Does that do it?- Hide quoted text -
>
- Show quoted text -

On Mar 4, 6:32 am, "Gord" <g...@kingston.netwrote:
>
>
>>>>>>>>>>>>>>>>
I get an error that says "undefined function 'ListYears' in Expression.

On Mar 4, 9:15 am, "Chris" <chrislab...@gmail.comwrote:
>
>
>
>
>>>>>>>>>>>>>>>>>>
1. Did you paste the VBA code into a new Access Module (a regular
Module, not a Class Module) and save it?
>
2. Are you are running the query from within Access?- Hide quoted text -
>
- Show quoted text -

On Mar 4, 10:03 am, "Gord" <g...@kingston.netwrote:
>
>
>>>>>>>>>>>>>>>>>>>>>>
What I did was clicked on modules under objects clicked new and pasted
the code in there saved it as ListYears then ran the query. Sorry if
im not writying it correctly but I am a newbie at this

On Mar 4, 10:28 am, "Chris" <chrislab...@gmail.comwrote:
>
>
>
>
>>>>>>>>>>>>>>>>>>>>>>>>
Aha. The name of the module shouldn't be the same as the name of the
function inside it. Rename the module to "modListYears" or "Module1"
or something, then try again.- Hide quoted text -
>
- Show quoted text -

On Mar 4, 10:41 am, "Gord" <g...@kingston.netwrote:
>
>
>>>>>>>>>>>>>>>>>>>>>>>>>>>
okay now I get a Run-time error '3061'
Too Few parameters. Expected 2
>
Thank you so much for your help

On Mar 4, 11:09 am, "Chris" <chrislab...@gmail.comwrote:
>
>
>
>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
What are the data types for the columns in your [AlsoPlayedFor] table?
I assumed that they were:
>
playerID: Long Integer
teamID: Long Integer
Year: Integer- Hide quoted text -
>
- Show quoted text -

On Mar 4, 12:29 pm, "Gord" <g...@kingston.netwrote:
>
>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
playerID: Text
teamID: Text
Year: Number

On Mar 4, 12:31 pm, "Chris" <chrislab...@gmail.comwrote:
>
>
>
>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Okay, use this code instead:
>
Public Function ListYears(playerID, teamID)
Dim _
rst As DAO.Recordset, _
prevYear As Integer, _
seqStart As Integer, _
strList As String
>
Set rst = CurrentDb.OpenRecordset( _
"SELECT [Year] FROM [AlsoPlayedFor] " & _
"WHERE [playerID]='" & playerID & "' " & _
"AND [teamID]='" & teamID & "' " & _
"ORDER BY [Year]", _
dbOpenSnapshot)
strList = ""
prevYear = 0
seqStart = 0
Do While Not rst.EOF
If rst!Year <prevYear + 1 Then
strList = strList & _
IIf(seqStart <prevYear, "-" & prevYear, "") & _
", " & _
rst!Year
seqStart = rst!Year
End If
prevYear = rst!Year
rst.MoveNext
Loop
rst.Close
Set rst = Nothing
ListYears = Mid(strList, 3)
End Function- Hide quoted text -
>
- Show quoted text -

On Mar 4, 12:50 pm, "Gord" <g...@kingston.netwrote:
>
>
>
>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
Your a Genius, thank you so much.....- Hide quoted text -
>
- Show quoted text -

On Mar 4, 1:03 pm, "Chris" <chrislab...@gmail.comwrote:
>
>
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
okay so I added some new records to my table now when I run the query
it only displayes the lowest year instead of groupig them together...