Custom Function
in [Queries]
|
Prev: Duplicate a record
Next: Access Queries
From: Dale on 25 Apr 2010 22:47 Hi Duane, you are so right! I've been working on this too long...I missed the first select and was only using one table, I've seen rectified and added another .field(i) and its working wonderfully. So elegant and simple your code was....Thank you Thank you -- Duane Hookom wrote: "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-concate na te-child-records_topic16&SID=6d2f3z28-ebf1-6cfe9zz6-def718z2-416z28z e. 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 22:54
That should have been I've since rectified....et la -- Dale wrote: Hi Duane, you are so right! I've been working on this too long...I missed the first select and was only using one table, I've seen rectified and added another .field(i) and its working wonderfully. So elegant and simple your code was....Thank you Thank you -- Duane Hookom wrote: "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-concate na te-child-records_topic16&SID=6d2f3z28-ebf1-6cfe9zz6-def718z2-416z28z e. 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. -- . |