Custom Function
in [Queries]
|
Prev: Duplicate a record
Next: Access Queries
From: Dale on 25 Apr 2010 09:55 I am trying to create a tool for yearly employee performance reviews: each employee is ranked on 5-10 different "competencies" and their behavior related to those competencies throughout the year. I have a table tracking those events. At year end I would like to create a report bound to a query that groups the employee by competency, avg rank for the competency and lists each observed behaviour for the entire year in its own field. The tblDailyObserved table lists Empname, Competency, Edate, Behavior, Rank EmployeeN Competency Rank Behavior John Smith Customer Focus 5 Text 1 Text 2 Text 3 I have this as a function so far and its not working: (HA! thought this would be simple!) Public Function AggCom(sEmpName, sCompetency,sEvent, sDate) As String On Error Resume Next Dim db As DAO.Database, rst As DAO.Recordset, sSQL As String Set db = CurrentDb sSQL = "SELECT EmployeeN, Competency, Edate, Event FROM tblDailyObserved ORDER BY EmployeeN, Competency ASC" Set rst = db.OpenRecordset(sSQL, dbOpenSnapshot) rst.MoveFirst Do While sEmpName = rst!EmployeeN And sCompetency = rst!Competency AggCom = sDate & ": " & sEvent & vbCrLf & vbCrLf & rst!EDate & ": " & rst!Event rst.MoveNext Loop Set rst = Nothing Set db = Nothing End Function Thanks in a advance for your help.
From: Duane Hookom on 25 Apr 2010 13:32 Why are you using code? Since this is a report, you should be able to create a subreport of the observed behaviors for the entire year. If you really think you need to place these into a single value, try the generic concatenate function found at http://www.rogersaccesslibrary.com/forum/generic-function-to-concatenate-child-records_topic16&SID=6d2f3z28-ebf1-6cfe9zz6-def718z2-416z28ze.html -- Duane Hookom MS Access MVP "Dale" <Dale(a)discussions.microsoft.com> wrote in message news:9A5F9C7E-C552-4C46-A223-DBD7342DF36B(a)microsoft.com... > I am trying to create a tool for yearly employee performance reviews: each > employee is ranked on 5-10 different "competencies" and their behavior > related to those competencies throughout the year. I have a table > tracking > those events. At year end I would like to create a report bound to a > query > that groups the employee by competency, avg rank for the competency and > lists > each observed behaviour for the entire year in its own field. > > The tblDailyObserved table lists Empname, Competency, Edate, Behavior, > Rank > > > EmployeeN Competency Rank Behavior > John Smith Customer Focus 5 Text 1 > Text 2 > Text 3 > > I have this as a function so far and its not working: (HA! thought this > would be simple!) > > Public Function AggCom(sEmpName, sCompetency,sEvent, sDate) As String > > On Error Resume Next > > Dim db As DAO.Database, rst As DAO.Recordset, sSQL As String > > Set db = CurrentDb > > sSQL = "SELECT EmployeeN, Competency, Edate, Event FROM tblDailyObserved > ORDER BY EmployeeN, Competency ASC" > > Set rst = db.OpenRecordset(sSQL, dbOpenSnapshot) > > rst.MoveFirst > > Do While sEmpName = rst!EmployeeN And sCompetency = rst!Competency > AggCom = sDate & ": " & sEvent & vbCrLf & vbCrLf & rst!EDate & ": " & > rst!Event > > rst.MoveNext > > Loop > > Set rst = Nothing > Set db = Nothing > > End Function > > Thanks in a advance for your help.
From: Dale on 25 Apr 2010 14:58 Thanks Duane, This concatenate function is only finding the first text blob if there are more than one. Actually I hadn't thought of a subreport..I presumed creating the query function would be the easiest method and is still my prefered by will look at a subreport. Duane Hookom wrote: Why are you using code? Since this is a report, you should be able to create a subreport of the observed behaviors for the entire year. If you really think you need to place these into a single value, try the generic concatenate function found at http://www.rogersaccesslibrary.com/forum/generic-function-to-concatena te-child-records_topic16&SID=6d2f3z28-ebf1-6cfe9zz6-def718z2-416z28ze. html -- Duane Hookom MS Access MVP "Dale" <Dale(a)discussions.microsoft.com> wrote in message news:9A5F9C7E-C552-4C46-A223-DBD7342DF36B(a)microsoft.com... I am trying to create a tool for yearly employee performance reviews: each employee is ranked on 5-10 different "competencies" and their behavior related to those competencies throughout the year. I have a table tracking those events. At year end I would like to create a report bound to a query that groups the employee by competency, avg rank for the competency and lists each observed behaviour for the entire year in its own field. The tblDailyObserved table lists Empname, Competency, Edate, Behavior, Rank EmployeeN Competency Rank Behavior John Smith Customer Focus 5 Text 1 Text 2 Text 3 I have this as a function so far and its not working: (HA! thought this would be simple!) Public Function AggCom(sEmpName, sCompetency,sEvent, sDate) As String On Error Resume Next Dim db As DAO.Database, rst As DAO.Recordset, sSQL As String Set db = CurrentDb sSQL = "SELECT EmployeeN, Competency, Edate, Event FROM tblDailyObserved ORDER BY EmployeeN, Competency ASC" Set rst = db.OpenRecordset(sSQL, dbOpenSnapshot) rst.MoveFirst Do While sEmpName = rst!EmployeeN And sCompetency = rst!Competency AggCom = sDate & ": " & sEvent & vbCrLf & vbCrLf & rst!EDate & ": " & rst!Event rst.MoveNext Loop Set rst = Nothing Set db = Nothing End Function Thanks in a advance for your help. --
From: KenSheridan via AccessMonster.com on 25 Apr 2010 17:19 The following link has an example of how a subreport's data can be laid out across the page, wrapping to a second line if necessary, using a multi-column subreport in across-then-down layout: http://community.netscape.com/n/pfx/forum.aspx?tsn=1&nav=libraryMessages&webtag=ws-msdevapps&tid=24271 The result tends to be clearer and more easily read than concatenating a series of values into a single string expression. The file also includes a means of achieving the same result in a single report by manipulating its layout in code at runtime, but that's only there to show that it can be done that way as I originally prepared the file in answer to a reader's request for such a solution to a contact of mine who used to write a databases column for a magazine. A subreport is far simpler and requires no code. Ken Sheridan Stafford, England Dale wrote: >Thanks Duane, > >This concatenate function is only finding the first text blob if there >are more than one. > >Actually I hadn't thought of a subreport..I presumed creating the query >function would be the easiest method and is still my prefered by will >look at a subreport. > >Why are you using code? Since this is a report, you should be able to >create a subreport of the observed behaviors for the entire year. > >If you really think you need to place these into a single value, try >the generic concatenate function found at >http://www.rogersaccesslibrary.com/forum/generic-function-to-concatena >te-child-records_topic16&SID=6d2f3z28-ebf1-6cfe9zz6-def718z2-416z28ze. >html > >I am trying to create a tool for yearly employee performance >reviews: each employee is ranked on 5-10 different "competencies" >and their behavior related to those competencies throughout the >year. I have a table tracking those events. At year end I would >like to create a report bound to a query that groups the employee >by competency, avg rank for the competency and lists each observed >behaviour for the entire year in its own field. > >The tblDailyObserved table lists Empname, Competency, Edate, >Behavior, Rank > >EmployeeN Competency Rank Behavior >John Smith Customer Focus 5 Text 1 > Text 2 > Text 3 > >I have this as a function so far and its not working: (HA! thought >this would be simple!) > >Public Function AggCom(sEmpName, sCompetency,sEvent, sDate) As >String > >On Error Resume Next > >Dim db As DAO.Database, rst As DAO.Recordset, sSQL As String > >Set db = CurrentDb > >sSQL = "SELECT EmployeeN, Competency, Edate, Event FROM >tblDailyObserved ORDER BY EmployeeN, Competency ASC" > >Set rst = db.OpenRecordset(sSQL, dbOpenSnapshot) > >rst.MoveFirst > >Do While sEmpName = rst!EmployeeN And sCompetency = rst!Competency > AggCom = sDate & ": " & sEvent & vbCrLf & vbCrLf & rst!EDate & >": " & rst!Event > >rst.MoveNext > >Loop > >Set rst = Nothing >Set db = Nothing > >End Function > >Thanks in a advance for your help. > >-- -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/201004/1
From: Duane Hookom on 25 Apr 2010 21:38
"This concatenate function is only finding the first text blob if there are more than one." Apparently you have done something wrong since the returned value is set up return multiple values if the expression is correct. If you need assistance then perhaps you should come back with table structures and the expression you tried. As much as I like code, the subreport method probably provides greater formatting options. -- Duane Hookom Microsoft Access MVP "Dale" wrote: > Thanks Duane, > > This concatenate function is only finding the first text blob if there > are more than one. > > Actually I hadn't thought of a subreport..I presumed creating the query > function would be the easiest method and is still my prefered by will > look at a subreport. > > > Duane Hookom wrote: > Why are you using code? Since this is a report, you should be able to > create a subreport of the observed behaviors for the entire year. > > If you really think you need to place these into a single value, try > the generic concatenate function found at > http://www.rogersaccesslibrary.com/forum/generic-function-to-concatena > te-child-records_topic16&SID=6d2f3z28-ebf1-6cfe9zz6-def718z2-416z28ze. > html > > > -- > Duane Hookom > MS Access MVP > > > "Dale" <Dale(a)discussions.microsoft.com> wrote in message > news:9A5F9C7E-C552-4C46-A223-DBD7342DF36B(a)microsoft.com... > I am trying to create a tool for yearly employee performance > reviews: each employee is ranked on 5-10 different "competencies" > and their behavior related to those competencies throughout the > year. I have a table tracking those events. At year end I would > like to create a report bound to a query that groups the employee > by competency, avg rank for the competency and lists each observed > behaviour for the entire year in its own field. > > The tblDailyObserved table lists Empname, Competency, Edate, > Behavior, Rank > > > EmployeeN Competency Rank Behavior > John Smith Customer Focus 5 Text 1 > Text 2 > Text 3 > > I have this as a function so far and its not working: (HA! thought > this would be simple!) > > Public Function AggCom(sEmpName, sCompetency,sEvent, sDate) As > String > > On Error Resume Next > > Dim db As DAO.Database, rst As DAO.Recordset, sSQL As String > > Set db = CurrentDb > > sSQL = "SELECT EmployeeN, Competency, Edate, Event FROM > tblDailyObserved ORDER BY EmployeeN, Competency ASC" > > Set rst = db.OpenRecordset(sSQL, dbOpenSnapshot) > > rst.MoveFirst > > Do While sEmpName = rst!EmployeeN And sCompetency = rst!Competency > AggCom = sDate & ": " & sEvent & vbCrLf & vbCrLf & rst!EDate & > ": " & rst!Event > > rst.MoveNext > > Loop > > Set rst = Nothing > Set db = Nothing > > End Function > > Thanks in a advance for your help. > > > > -- > > . > |