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 12 1375
On Mar 3, 11:54 pm, "Chris" <chrislab...@gmail.comwrote:
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
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?
On Mar 4, 6:32 am, "Gord" <g...@kingston.netwrote:
On Mar 3, 11:54 pm, "Chris" <chrislab...@gmail.comwrote:
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
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 -
I get an error that says "undefined function 'ListYears' in Expression.
On Mar 4, 9:15 am, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 6:32 am, "Gord" <g...@kingston.netwrote:
On Mar 3, 11:54 pm, "Chris" <chrislab...@gmail.comwrote:
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
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 -
I get an error that says "undefined function 'ListYears' in Expression.
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?
On Mar 4, 10:03 am, "Gord" <g...@kingston.netwrote:
On Mar 4, 9:15 am, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 6:32 am, "Gord" <g...@kingston.netwrote:
On Mar 3, 11:54 pm, "Chris" <chrislab...@gmail.comwrote:
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
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 -
I get an error that says "undefined function 'ListYears' in Expression.
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 -
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:
On Mar 4, 10:03 am, "Gord" <g...@kingston.netwrote:
On Mar 4, 9:15 am, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 6:32 am, "Gord" <g...@kingston.netwrote:
On Mar 3, 11:54 pm, "Chris" <chrislab...@gmail.comwrote:
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
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 -
I get an error that says "undefined function 'ListYears' in Expression.
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 -
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
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.
On Mar 4, 10:41 am, "Gord" <g...@kingston.netwrote:
On Mar 4, 10:28 am, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 10:03 am, "Gord" <g...@kingston.netwrote:
On Mar 4, 9:15 am, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 6:32 am, "Gord" <g...@kingston.netwrote:
On Mar 3, 11:54 pm, "Chris" <chrislab...@gmail.comwrote:
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
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 -
I get an error that says "undefined function 'ListYears' in Expression.
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 -
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
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 -
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:
On Mar 4, 10:41 am, "Gord" <g...@kingston.netwrote:
On Mar 4, 10:28 am, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 10:03 am, "Gord" <g...@kingston.netwrote:
On Mar 4, 9:15 am, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 6:32 am, "Gord" <g...@kingston.netwrote:
On Mar 3, 11:54 pm, "Chris" <chrislab...@gmail.comwrote:
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
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 -
I get an error that says "undefined function 'ListYears' in Expression.
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 -
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
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 -
okay now I get a Run-time error '3061'
Too Few parameters. Expected 2
Thank you so much for your help
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
On Mar 4, 12:29 pm, "Gord" <g...@kingston.netwrote:
On Mar 4, 11:09 am, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 10:41 am, "Gord" <g...@kingston.netwrote:
On Mar 4, 10:28 am, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 10:03 am, "Gord" <g...@kingston.netwrote:
On Mar 4, 9:15 am, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 6:32 am, "Gord" <g...@kingston.netwrote:
On Mar 3, 11:54 pm, "Chris" <chrislab...@gmail.comwrote:
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
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 -
I get an error that says "undefined function 'ListYears' in Expression.
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 -
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
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 -
okay now I get a Run-time error '3061'
Too Few parameters. Expected 2
Thank you so much for your help
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 -
playerID: Text
teamID: Text
Year: Number
On Mar 4, 12:31 pm, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 12:29 pm, "Gord" <g...@kingston.netwrote:
On Mar 4, 11:09 am, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 10:41 am, "Gord" <g...@kingston.netwrote:
On Mar 4, 10:28 am, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 10:03 am, "Gord" <g...@kingston.netwrote:
On Mar 4, 9:15 am, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 6:32 am, "Gord" <g...@kingston.netwrote:
On Mar 3, 11:54 pm, "Chris" <chrislab...@gmail.comwrote:
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
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 -
I get an error that says "undefined function 'ListYears' in Expression.
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 -
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
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 -
okay now I get a Run-time error '3061'
Too Few parameters. Expected 2
Thank you so much for your help
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 -
playerID: Text
teamID: Text
Year: Number
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
On Mar 4, 12:50 pm, "Gord" <g...@kingston.netwrote:
On Mar 4, 12:31 pm, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 12:29 pm, "Gord" <g...@kingston.netwrote:
On Mar 4, 11:09 am, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 10:41 am, "Gord" <g...@kingston.netwrote:
On Mar 4, 10:28 am, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 10:03 am, "Gord" <g...@kingston.netwrote:
On Mar 4, 9:15 am, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 6:32 am, "Gord" <g...@kingston.netwrote:
On Mar 3, 11:54 pm, "Chris" <chrislab...@gmail.comwrote:
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
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 -
I get an error that says "undefined function 'ListYears' in Expression.
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 -
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
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 -
okay now I get a Run-time error '3061'
Too Few parameters. Expected 2
Thank you so much for your help
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 -
playerID: Text
teamID: Text
Year: Number
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 -
Your a Genius, thank you so much.....
On Mar 4, 1:03 pm, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 12:50 pm, "Gord" <g...@kingston.netwrote:
On Mar 4, 12:31 pm, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 12:29 pm, "Gord" <g...@kingston.netwrote:
On Mar 4, 11:09 am, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 10:41 am, "Gord" <g...@kingston.netwrote:
On Mar 4, 10:28 am, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 10:03 am, "Gord" <g...@kingston.netwrote:
On Mar 4, 9:15 am, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 6:32 am, "Gord" <g...@kingston.netwrote:
On Mar 3, 11:54 pm, "Chris" <chrislab...@gmail.comwrote:
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
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 -
I get an error that says "undefined function 'ListYears' in Expression.
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 -
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
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 -
okay now I get a Run-time error '3061'
Too Few parameters. Expected 2
Thank you so much for your help
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 -
playerID: Text
teamID: Text
Year: Number
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 -
Your a Genius, thank you so much.....- Hide quoted text -
- Show quoted text -
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...
On Mar 4, 1:42 pm, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 1:03 pm, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 12:50 pm, "Gord" <g...@kingston.netwrote:
On Mar 4, 12:31 pm, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 12:29 pm, "Gord" <g...@kingston.netwrote:
On Mar 4, 11:09 am, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 10:41 am, "Gord" <g...@kingston.netwrote:
On Mar 4, 10:28 am, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 10:03 am, "Gord" <g...@kingston.netwrote:
On Mar 4, 9:15 am, "Chris" <chrislab...@gmail.comwrote:
On Mar 4, 6:32 am, "Gord" <g...@kingston.netwrote:
On Mar 3, 11:54 pm, "Chris" <chrislab...@gmail.comwrote:
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
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 -
I get an error that says "undefined function 'ListYears' in Expression.
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 -
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
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 -
okay now I get a Run-time error '3061'
Too Few parameters. Expected 2
Thank you so much for your help
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 -
playerID: Text
teamID: Text
Year: Number
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 -
Your a Genius, thank you so much.....- Hide quoted text -
- Show quoted text -
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...
Rats. End conditions. Try this:
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)
If rst.EOF Then
ListYears = ""
Else
strList = ""
prevYear = 0
seqStart = 0
Do While Not rst.EOF
If rst!Year <prevYear + 1 Then
strList = strList & _
IIf(prevYear <seqStart, "-" & prevYear, "") & _
", " & _
rst!Year
seqStart = rst!Year
End If
prevYear = rst!Year
rst.MoveNext
Loop
If prevYear <seqStart Then
strList = strList & "-" & prevYear
End If
ListYears = Mid(strList, 3)
End If
rst.Close
Set rst = Nothing
End Function This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: a |
last post by:
We are writing an app that assigns people to teams based on their curent
score. Teams are 8 people, there are 2 teams. (i would like it to be
flexible, but this is a start). I need an algorithm...
|
by: joh12005 |
last post by:
hello,
i'm looking for a way to have a list of number grouped by consecutive
interval, after a search, for example :
=>
, , , ]
|
by: Erik Nodland |
last post by:
Hi Everyone,
Just after some ideas and suggestions on how to do this. I have a large
amount of numeric data which I would like to group given a distance as a
parameter.
IE. If my dataset was...
|
by: Roman Bertle |
last post by:
Hello,
I try to format monetary values using the locale module, python2.5:
Python 2.5.2a0 (r251:54863, Jan 3 2008, 17:59:56)
on linux2
Type "help", "copyright", "credits" or "license" for...
|
by: patjones |
last post by:
Good afternoon:
This seems like it shouldn't be hard, and then again this is how so many problems seem at the outset. My situation is this:
I have a report called rptMain319, which is based...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| |