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

Katie Fitzpatrick <(E-Mail Removed)> wrote in
news:bc8984ec-7964-490c-bde7-(E-Mail Removed)
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/

On Jul 21, 1:50*pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
wrote:
> Katie Fitzpatrick <katepass...@gmail.com> wrote innews:bc8984ec-7964-490c-bde7-(E-Mail Removed)
> 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/

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

On Jul 21, 1:50*pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
wrote:
> Katie Fitzpatrick <katepass...@gmail.com> wrote innews:bc8984ec-7964-490c-bde7-(E-Mail Removed)
> 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"

On Jul 21, 2:07*pm, Katie Fitzpatrick <katepass...@gmail.com> wrote:
> On Jul 21, 1:50*pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
> wrote:
>
>
>
>
>
> > Katie Fitzpatrick <katepass...@gmail.com> wrote innews:bc8984ec-7964-490c-bde7-(E-Mail Removed)
> > 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!

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

Katie Fitzpatrick wrote:
> On Jul 21, 2:07 pm, Katie Fitzpatrick <katepass...@gmail.com> wrote:
>> On Jul 21, 1:50 pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
>> wrote:
>>
>>> Katie Fitzpatrick <katepass...@gmail.com> wrote innews:bc8984ec-7964-490c-bde7-(E-Mail Removed)
>>> 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!

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.



On Jul 22, 8:35*am, John Spencer <JSPEN...@Hilltop.umbc> wrote:
> 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
>
>
>
> Katie Fitzpatrick wrote:
> > On Jul 21, 2:07 pm, Katie Fitzpatrick <katepass...@gmail.com> wrote:
> >> On Jul 21, 1:50 pm, "David W. Fenton" <XXXuse...@dfenton.com.invalid>
> >> wrote:
>
> >>> Katie Fitzpatrick <katepass...@gmail.com> wrote innews:bc8984ec-7964-490c-bde7-(E-Mail Removed)
> >>> 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!