Passing Parameters

[OldManEd]

I have 2 tables, (1) group table and (2) member table. There is a one to
many relationship: each group has several members in member tables but no
member belongs to more than one group. The linking relationship is a group
ID.

I have created a function that uses this group ID to find all the records in
the members table with the same group ID and create a string of data from
each record and then concatenating a field value. This function works in the
VBA intermediate window when I manually type in an ID:

Function(ID as variant) as variant
code
End Function

Now I want to call this function in a report. How do I proceed?

 

[Rob Parker]

There's a couple of ways of doing this:

First, you could base your report on a query, and include the function as a
calculated field in the query (that's the way I'd do it; it lets me know
I've got the records correct before I get into the report design).

Alternatively, you can add an unbound textbox to the appropriate section of
your report (most likely the Detail section), and set its Control Source to
the expression:
=Function([ID])
You'll need to include the [ID] field as a bound control from the report's
Record Source, but you can set its visibility to No if you don't want it to
show.

HTH,

Rob

 

[OldManEd]

How does one make a query using a function as a calculated field?

What is the SQL statement?

Something like: "SELECT [name] FROM tblName WHERE [ID] = function(ID)???

Ed

Does this produce one name per line in the view?

Ed

There's a couple of ways of doing this:

First, you could base your report on a query, and include the function as
a calculated field in the query (that's the way I'd do it; it lets me know
I've got the records correct before I get into the report design).

Alternatively, you can add an unbound textbox to the appropriate section
of your report (most likely the Detail section), and set its Control
Source to the expression:
=Function([ID])
You'll need to include the [ID] field as a bound control from the report's
Record Source, but you can set its visibility to No if you don't want it
to show.

HTH,

Rob

I have 2 tables, (1) group table and (2) member table. There is a one
to many relationship: each group has several members in member tables
but no member belongs to more than one group. The linking
relationship is a group ID.

I have created a function that uses this group ID to find all the
records in the members table with the same group ID and create a
string of data from each record and then concatenating a field value.
This function works in the VBA intermediate window when I manually
type in an ID:
Function(ID as variant) as
variant code
End Function

Now I want to call this function in a report. How do I proceed?

 

[John Spencer]

Something like

SELECT [Name, TheFunction(TheArguments) as aResult
FROM SomeTable

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

How does one make a query using a function as a calculated field?

What is the SQL statement?

Something like: "SELECT [name] FROM tblName WHERE [ID] = function(ID)???

Ed

Does this produce one name per line in the view?

Ed

There's a couple of ways of doing this:

First, you could base your report on a query, and include the function
as a calculated field in the query (that's the way I'd do it; it lets
me know I've got the records correct before I get into the report
design).

Alternatively, you can add an unbound textbox to the appropriate
section of your report (most likely the Detail section), and set its
Control Source to the expression:
=Function([ID])
You'll need to include the [ID] field as a bound control from the
report's Record Source, but you can set its visibility to No if you
don't want it to show.

HTH,

Rob

I have 2 tables, (1) group table and (2) member table. There is a one
to many relationship: each group has several members in member tables
but no member belongs to more than one group. The linking
relationship is a group ID.

I have created a function that uses this group ID to find all the
records in the members table with the same group ID and create a
string of data from each record and then concatenating a field value.
This function works in the VBA intermediate window when I manually
type in an ID:
Function(ID as variant) as
variant code
End Function

Now I want to call this function in a report. How do I proceed?

 

[John Spencer]

Typo


SELECT [Name]
, TheFunction(TheArgument) as aResult
FROM SomeTable

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Something like

SELECT [Name, TheFunction(TheArguments) as aResult
FROM SomeTable

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

How does one make a query using a function as a calculated field?

What is the SQL statement?

Something like: "SELECT [name] FROM tblName WHERE [ID] = function(ID)???

Ed

Does this produce one name per line in the view?

Ed

There's a couple of ways of doing this:

First, you could base your report on a query, and include the
function as a calculated field in the query (that's the way I'd do
it; it lets me know I've got the records correct before I get into
the report design).

Alternatively, you can add an unbound textbox to the appropriate
section of your report (most likely the Detail section), and set its
Control Source to the expression:
=Function([ID])
You'll need to include the [ID] field as a bound control from the
report's Record Source, but you can set its visibility to No if you
don't want it to show.

HTH,

Rob


OldManEd wrote:
I have 2 tables, (1) group table and (2) member table. There is a one
to many relationship: each group has several members in member tables
but no member belongs to more than one group. The linking
relationship is a group ID.

I have created a function that uses this group ID to find all the
records in the members table with the same group ID and create a
string of data from each record and then concatenating a field value.
This function works in the VBA intermediate window when I manually
type in an ID:
Function(ID as variant) as
variant code
End Function

Now I want to call this function in a report. How do I proceed?