Custom Function

[Dale]

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.

 

[Duane Hookom]

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/...6d2f3z28-ebf1-6cfe9zz6-def718z2-416z28ze.html

 

[Dale]

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


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.



--

 

[KenSheridan via AccessMonster.com]

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...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

 

[Duane Hookom]

"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.

 

[Dale]

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


:

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


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.



--

 

[Dale]

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


:

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


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.



--