Assistance with calling UDF from a query

[Katie Fitzpatrick]

Hello all -
I *know* this has been covered many places, but my hours of searching
has yielded me no resolution to what I imagine is a pretty simple
problem.

I have one table: Sample
There two columns I'm interested in are: UserId and Role

I am (attempting) to use Dev Ashish's fConcatFld function to produce
a
simple select statement that would produce the following:

Before:

UserID Role
Sam ABC System
Sam XYZ System
Joe ABC System
Joe ABC System Admin
Sam ABC Admin


After:

UserID Role
Sam ABC System, XYZ System, ABC Admin
Joe ABC System, ABC System Admin


I followed the instructions to enter a code window, insert a module,
pasted the code, ran compile under the debug menu.

Then I entered the following into a query:
SELECT Userid, fConcatFld("Sample","UserID","Role","string",[Userid])
AS Roles
FROM Sample
GROUP BY [Userid];

The error I receive is as follows:
"Undefined Function 'fConcatFld" in expression"
The function is declared as public - what am I missing?
Thank you so much for your assistance with this question.
(accidentally originally posted under microsoft.public.access.forms -
I posted a retraction and now am posting here.)

 

[David W. Fenton]

m:

I followed the instructions to enter a code window, insert a
module, pasted the code, ran compile under the debug menu.

Then I entered the following into a query:
SELECT Userid,
fConcatFld("Sample","UserID","Role","string",[Userid]) AS Roles
FROM Sample
GROUP BY [Userid];

The error I receive is as follows:
"Undefined Function 'fConcatFld" in expression"
The function is declared as public - what am I missing?

Can you run it in the Immediate window?

 

[Katie Fitzpatrick]

I followed the instructions to enter a code window, insert a
module, pasted the code, ran compile under the debug menu.

Then I entered the following into a query:
SELECT Userid,
fConcatFld("Sample","UserID","Role","string",[Userid]) AS Roles
FROM Sample
GROUP BY [Userid];

The error I receive is as follows:
"Undefined Function 'fConcatFld" in expression"
The function is declared as public - what am I missing?

Can you run it in the Immediate window?

Thanks for your reply. I'm not sure I understand - could you clarify
your question?

 

[Katie Fitzpatrick]

I followed the instructions to enter a code window, insert a
module, pasted the code, ran compile under the debug menu.

Then I entered the following into a query:
SELECT Userid,
fConcatFld("Sample","UserID","Role","string",[Userid]) AS Roles
FROM Sample
GROUP BY [Userid];

The error I receive is as follows:
"Undefined Function 'fConcatFld" in expression"
The function is declared as public - what am I missing?

Can you run it in the Immediate window?

Okay - I googled instead of expecting you to write it out.

In the immediate window I entered: ?fConcatFld and received "Compile
Error: Type Mismatch"

 

[Katie Fitzpatrick]

m:

I followed the instructions to enter a code window, insert a
module, pasted the code, ran compile under the debug menu.
Then I entered the following into a query:
SELECT Userid,
fConcatFld("Sample","UserID","Role","string",[Userid]) AS Roles
FROM Sample
GROUP BY [Userid];
The error I receive is as follows:
"Undefined Function 'fConcatFld" in expression"
The function is declared as public - what am I missing?

Can you run it in the Immediate window?

Okay - I googled instead of expecting you to write it out.

In the immediate window I entered: ?fConcatFld and received "Compile
Error: Type Mismatch"

Guess folks -

I found part of the problem, I had named the module the same name as
the function and it confused the query.

Now it's running (a step forward) and giving me a different error
"Error #: 20".

I will search on this!

 

[John Spencer]

Any description of the error to go with that error number?

What is the datatype of UserID? Is it a text field or is it a number field?

I see that your sample data shows a text string for UserID, but if that is a
lookup field in a table the actual value could be a number while DISPLAYING in
the table and query datasheet view the associated text instead of the actual
value stored in the table.

If UserID is a number field, then the call should be
fConcatFld("Sample","UserID","Role","Long",[Userid])


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

m:
I followed the instructions to enter a code window, insert a
module, pasted the code, ran compile under the debug menu.
Then I entered the following into a query:
SELECT Userid,
fConcatFld("Sample","UserID","Role","string",[Userid]) AS Roles
FROM Sample
GROUP BY [Userid];
The error I receive is as follows:
"Undefined Function 'fConcatFld" in expression"
The function is declared as public - what am I missing?
Can you run it in the Immediate window?

Okay - I googled instead of expecting you to write it out.

In the immediate window I entered: ?fConcatFld and received "Compile
Error: Type Mismatch"

Guess folks -

I found part of the problem, I had named the module the same name as
the function and it confused the query.

Now it's running (a step forward) and giving me a different error
"Error #: 20".

I will search on this!

 

[Katie Fitzpatrick]

Hi John, thanks for responding.

The error appears to loop through error #0, and error#20, but it only
said, "go to error" as a message.

It appears I had a few problems, but it is working now. For
documentation's sake, here were my errors.

I named the function the same as the module, so it wouldn't call the
function properly.

also, I had fConcatFld("Sample","UserID","Role","Text",[Userid])
instead of
fConcatFld("Sample","UserID","Role","string",[Userid])

It is working properly now, I appreciate everyone's help.

Any description of the error to go with that error number?

What is the datatype of UserID?  Is it a text field or is it a number field?

I see that your sample data shows a text string for UserID, but if that is a
lookup field in a table the actual value could be a number while DISPLAYING in
the table and query datasheet view the associated text instead of the actual
value stored in the table.

If UserID is a number field, then the call should be
fConcatFld("Sample","UserID","Role","Long",[Userid])

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

On Jul 21, 1:50 pm, "David W. Fenton" <[email protected]>
wrote:
m:
I followed the instructions to enter a code window, insert a
module, pasted the code, ran compile under the debug menu.
Then I entered the following into a query:
SELECT Userid,
fConcatFld("Sample","UserID","Role","string",[Userid]) AS Roles
FROM Sample
GROUP BY [Userid];
The error I receive is as follows:
"Undefined Function 'fConcatFld" in expression"
The function is declared as public - what am I missing?
Can you run it in the Immediate window?
--
David W. Fenton                  http://www.dfenton..com/
usenet at dfenton dot com    http://www.dfenton.com/DFA/
Okay - I googled instead of expecting you to write it out.
In the immediate window I entered: ?fConcatFld and received "Compile
Error: Type Mismatch"

Guess folks -

I found part of the problem, I had named the module the same name as
the function and it confused the query.

Now it's running (a step forward) and giving me a different error
"Error #: 20".

I will search on this!