From: Dale on
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
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.



--

.

First  |  Prev  | 
Pages: 1 2
Prev: Duplicate a record
Next: Access Queries