分组号码 | RST

本文介绍了分组号码的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述我有一张桌子，其中列出了球员名单，参赛队伍和他们在那里玩的年，我的代码看起来像这样。 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; ，取最小年份和最大年份，并显示为年 - 年份但是，比方说玩家玩了5年，所以1990年， 1991,1992,1993,1995 它会显示为1990-1995，但我希望它显示为1990-1993， 1995，这是否可行？另外，如果年是1990年，1992年，1993年，1994年，1995年我希望它显示为1990， 1992-1995。 br /> 请帮助解决方案克里斯。我看到你还在努力解决这个问题。给这个 a尝试： 1.将以下代码粘贴到Access数据库中的新模块中： Public Function ListYears（playerID，teamID） Dim _ rst作为DAO.Recordset，_ prevYear As Integer，_ seqStart As Integer，_ strList As String 设置rst = CurrentDb.OpenRecordset（_ " ; SELECT [Year] FROM [AlsoPlayedFor]"& _ " WHERE [playerID] ="& playerID&""& _ " AND [teamID] ="& teamID&""& _ " ORDER BY [Year]"，_ dbOpenSnapshot ） strList ="" prevYear = 0 seqStart = 0 Do While Notrst。 EOF 如果是rst！年< prevYear + 1那么 strList = strList& _ IIf（seqStart< prevYear，" - "& prevYear，""）& _ "，" &安培; _ rst！年 seqStart = rst！年结束如果 prevYear = rst！年 rst.MoveNext 循环 rst.Close Set rst = Nothing ListYears = Mid（strList，3）结束函数 2.将查询更改为 ListYears（[playerID]，[teamID]）AS Years FROM AlsoPlayedFor GROUP BY AlsoPlayedFor.playerID，AlsoPlayedFor.teamID; 这样做吗？克里斯。我看到你还在努力解决这个问题。给这个 a尝试： 1.将以下代码粘贴到Access数据库中的新模块中： Public Function ListYears（playerID，teamID） Dim _ rst作为DAO.Recordset，_ prevYear As Integer，_ seqStart As Integer，_ strList As String 设置rst = CurrentDb.OpenRecordset（_ " ; SELECT [Year] FROM [AlsoPlayedFor]"& _ " WHERE [playerID] ="& playerID&""& _ " AND [teamID] ="& teamID&""& _ " ORDER BY [Year]"，_ dbOpenSnapshot ） strList ="" prevYear = 0 seqStart = 0 Do While Notrst。 EOF 如果是rst！年< prevYear + 1那么 strList = strList& _ IIf（seqStart< prevYear，" - "& prevYear，""）& _ "，" &安培; _ rst！年 seqStart = rst！年结束如果 prevYear = rst！年 rst.MoveNext 循环 rst.Close Set rst = Nothing ListYears =中（strList，3）结束功能 2.将查询更改为 SELECT AlsoPlayedFor。 playerID，AlsoPlayedFor.teamID， ListYears（[playerID]，[teamID]）AS Years FROM AlsoPlayedFor GROUP BY AlsoPlayedFor.playerID，另外播放了.teamID; 这样做吗？ - 隐藏引用的文字 - - 显示引用的文字 - 我在表达式中收到undefined function'ListYears'的错误。我在Expression中收到一条错误，上面写着未定义的函数，ListYears。 1.您是否将VBA代码粘贴到新的访问模块（常规模块，而不是类模块）并保存？ 2.您是否在Access中运行查询？ I have a table which lists player names, teams played for and theyears 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 AlsoPlayedForGROUP 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 theyears 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 thisa 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 = 0seqStart = 0Do While Not rst.EOFIf rst!Year <prevYear + 1 ThenstrList = strList & _IIf(seqStart <prevYear, "-" & prevYear, "") & _", " & _rst!YearseqStart = rst!YearEnd IfprevYear = rst!Yearrst.MoveNextLooprst.CloseSet rst = NothingListYears = Mid(strList, 3)End Function2. Change your query to SELECT AlsoPlayedFor.playerID, AlsoPlayedFor.teamID,ListYears([playerID],[teamID]) AS YearsFROM AlsoPlayedForGROUP BY AlsoPlayedFor.playerID, AlsoPlayedFor.teamID;Does that do it? Hi, Chris. I see you''re still struggling with this problem. Give thisa 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 StringSet rst = CurrentDb.OpenRecordset( _ "SELECT [Year] FROM [AlsoPlayedFor] " & _ "WHERE [playerID]=" & playerID & " " & _ "AND [teamID]=" & teamID & " " & _ "ORDER BY [Year]", _ dbOpenSnapshot)strList = ""prevYear = 0seqStart = 0Do 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.MoveNextLooprst.CloseSet rst = NothingListYears = Mid(strList, 3)End Function2. Change your query toSELECT AlsoPlayedFor.playerID, AlsoPlayedFor.teamID, ListYears([playerID],[teamID]) AS YearsFROM AlsoPlayedForGROUP 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. I get an error that says "undefined function ''ListYears'' in Expression.1. Did you paste the VBA code into a new Access Module (a regularModule, not a Class Module) and save it? 2. Are you are running the query from within Access? 这篇关于分组号码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！