Adding a function/module to Access 2000

[Alex]

Hi all,

I've found a module that I think will help me combine fields properly, but I'm unsure how to add or use it with Access
2000. Below is the module I'd like to add:

http://www.mvps.org/access/modules/mdl0008.htm

In Access, I goto Modules and create new. Leaving 'Option Compare Database' on top I paste the module code in and save
- naming it fConcatFld. It saves fine, but when I use fConcatFld in my query, like below:

SELECT PtName, fConcatFld("PtN otes") as Combined
FROM TESTCMSSPatient NotesQ001
Group by PtName

It errors out saying fConcatFld is an invalid function. I might have this entire process wrong... so how do I add this
function to Access so I can use it?

Thanks,

Alex.

[CDB]

Re: Adding a function/module to Access 2000

Alex, have you checked that the module compiles? In the VB editor, call
Debug/Compile menu.

You may have troubled with these lines:
Dim lodb As Database, lors As Recordset
Dim lovConcat As Variant, loCriteria As String
Dim loSQL As String
Const cQ = """"

1. You need to ensure that Tools/References includes Microsoft DAO 3.6
Library, and it is selected.

2. Change two of the above to
Dim lodb As DAO.Database
Dim lors As DAO.Recordset

Clive

"Alex" <alex@totallyne rd.com> wrote in message
news:dvSdnT_uto G9txXdRVn-uQ@sysmatrix.ne t...[color=blue]
> Hi all,
>
> I've found a module that I think will help me combine fields properly, but[/color]
I'm unsure how to add or use it with Access[color=blue]
> 2000. Below is the module I'd like to add:
>
> http://www.mvps.org/access/modules/mdl0008.htm
>
> In Access, I goto Modules and create new. Leaving 'Option Compare[/color]
Database' on top I paste the module code in and save[color=blue]
> - naming it fConcatFld. It saves fine, but when I use fConcatFld in my[/color]
query, like below:[color=blue]
>
> SELECT PtName, fConcatFld("PtN otes") as Combined
> FROM TESTCMSSPatient NotesQ001
> Group by PtName
>
> It errors out saying fConcatFld is an invalid function. I might have this[/color]
entire process wrong... so how do I add this[color=blue]
> function to Access so I can use it?
>
> Thanks,
>
> Alex.[/color]

[Bradley]

Re: Adding a function/module to Access 2000

Alex wrote:[color=blue]
> Hi all,
>
> I've found a module that I think will help me combine fields
> properly, but I'm unsure how to add or use it with Access 2000.
> Below is the module I'd like to add:
>
> http://www.mvps.org/access/modules/mdl0008.htm
>
> In Access, I goto Modules and create new. Leaving 'Option Compare
> Database' on top I paste the module code in and save - naming it
> fConcatFld. It saves fine, but when I use fConcatFld in my query,
> like below:
>
> SELECT PtName, fConcatFld("PtN otes") as Combined
> FROM TESTCMSSPatient NotesQ001
> Group by PtName
>
> It errors out saying fConcatFld is an invalid function. I might have
> this entire process wrong... so how do I add this function to Access
> so I can use it?
>
> Thanks,
>
> Alex.[/color]

Read the page you got the code from...

You need the function from the NorthWind database that comes as a sample
with Access.

"Using the fConcatFld function, in the Northwind database..."

^^^^^^^^^^^^^^^ ^^^^^^^^

:P
--
regards,

Bradley

[Alex]

Re: Adding a function/module to Access 2000

"Bradley" <bradley@REMOVE THIScomcen.com. au> wrote in message news:<40887047$ 1@nexus.comcen. com.au>...[color=blue]
> Alex wrote:[color=green]
> > Hi all,
> >
> > I've found a module that I think will help me combine fields
> > properly, but I'm unsure how to add or use it with Access 2000.
> > Below is the module I'd like to add:
> >
> > http://www.mvps.org/access/modules/mdl0008.htm
> >
> > In Access, I goto Modules and create new. Leaving 'Option Compare
> > Database' on top I paste the module code in and save - naming it
> > fConcatFld. It saves fine, but when I use fConcatFld in my query,
> > like below:
> >
> > SELECT PtName, fConcatFld("PtN otes") as Combined
> > FROM TESTCMSSPatient NotesQ001
> > Group by PtName
> >
> > It errors out saying fConcatFld is an invalid function. I might have
> > this entire process wrong... so how do I add this function to Access
> > so I can use it?
> >
> > Thanks,
> >
> > Alex.[/color]
>
> Read the page you got the code from...
>
> You need the function from the NorthWind database that comes as a sample
> with Access.
>
> "Using the fConcatFld function, in the Northwind database..."
>
> ^^^^^^^^^^^^^^^ ^^^^^^^^
>
> :P[/color]

Hi,

The give the code to add to *any* database, but use Northwind as an
example. From what I read the code isn't specific to Northwind, else
what use would giving an example code with such a disclaimer in the
comments be used for?

Thanks for the comments...

Alex

[Bradley]

Re: Adding a function/module to Access 2000

Alex wrote:[color=blue]
> "Bradley" <bradley@REMOVE THIScomcen.com. au> wrote in message
> news:<40887047$ 1@nexus.comcen. com.au>...[color=green]
>> Alex wrote:[color=darkred]
>>> Hi all,
>>>
>>> I've found a module that I think will help me combine fields
>>> properly, but I'm unsure how to add or use it with Access 2000.
>>> Below is the module I'd like to add:
>>>
>>> http://www.mvps.org/access/modules/mdl0008.htm
>>>
>>> In Access, I goto Modules and create new. Leaving 'Option Compare
>>> Database' on top I paste the module code in and save - naming it
>>> fConcatFld. It saves fine, but when I use fConcatFld in my query,
>>> like below:
>>>
>>> SELECT PtName, fConcatFld("PtN otes") as Combined
>>> FROM TESTCMSSPatient NotesQ001
>>> Group by PtName
>>>
>>> It errors out saying fConcatFld is an invalid function. I might
>>> have this entire process wrong... so how do I add this function to
>>> Access so I can use it?
>>>
>>> Thanks,
>>>
>>> Alex.[/color]
>>
>> Read the page you got the code from...
>>
>> You need the function from the NorthWind database that comes as a
>> sample with Access.
>>
>> "Using the fConcatFld function, in the Northwind database..."
>>
>> ^^^^^^^^^^^^^^^ ^^^^^^^^
>>[color=darkred]
>>> P[/color][/color]
>
> Hi,
>
> The give the code to add to *any* database, but use Northwind as an
> example. From what I read the code isn't specific to Northwind, else
> what use would giving an example code with such a disclaimer in the
> comments be used for?
>
> Thanks for the comments...
>
> Alex[/color]

You need to cut and paste into your database the function fConcatFld()
function found in the modules in the Northwind database.

The page says it depends on a function that is in the Northwind
database... so you need a copy of that as well. Read the web page
carefully.
--
regards,

Bradley

[Alex]

Re: Adding a function/module to Access 2000

"Bradley" <bradley@REMOVE THIScomcen.com. au> wrote in message news:<4088e80a$ 1@nexus.comcen. com.au>...[color=blue]
> Alex wrote:[color=green]
> > "Bradley" <bradley@REMOVE THIScomcen.com. au> wrote in message
> > news:<40887047$ 1@nexus.comcen. com.au>...[color=darkred]
> >> Alex wrote:
> >>> Hi all,
> >>>
> >>> I've found a module that I think will help me combine fields
> >>> properly, but I'm unsure how to add or use it with Access 2000.
> >>> Below is the module I'd like to add:
> >>>
> >>> http://www.mvps.org/access/modules/mdl0008.htm
> >>>
> >>> In Access, I goto Modules and create new. Leaving 'Option Compare
> >>> Database' on top I paste the module code in and save - naming it
> >>> fConcatFld. It saves fine, but when I use fConcatFld in my query,
> >>> like below:
> >>>
> >>> SELECT PtName, fConcatFld("PtN otes") as Combined
> >>> FROM TESTCMSSPatient NotesQ001
> >>> Group by PtName
> >>>
> >>> It errors out saying fConcatFld is an invalid function. I might
> >>> have this entire process wrong... so how do I add this function to
> >>> Access so I can use it?
> >>>
> >>> Thanks,
> >>>
> >>> Alex.
> >>
> >> Read the page you got the code from...
> >>
> >> You need the function from the NorthWind database that comes as a
> >> sample with Access.
> >>
> >> "Using the fConcatFld function, in the Northwind database..."
> >>
> >> ^^^^^^^^^^^^^^^ ^^^^^^^^
> >>
> >>> P[/color]
> >
> > Hi,
> >
> > The give the code to add to *any* database, but use Northwind as an
> > example. From what I read the code isn't specific to Northwind, else
> > what use would giving an example code with such a disclaimer in the
> > comments be used for?
> >
> > Thanks for the comments...
> >
> > Alex[/color]
>
> You need to cut and paste into your database the function fConcatFld()
> function found in the modules in the Northwind database.
>
> The page says it depends on a function that is in the Northwind
> database... so you need a copy of that as well. Read the web page
> carefully.[/color]


Hi.

I guess that's the problem... where to I paste the function into
Access? There's no obvious place called 'Functions' where you put
stuff like this. I tried adding it as a Module, but didn't work...
says Unknown Function.

So back to my root question... where the heck do I put the Function
code in Access so queries will see the custom function?

Thanks,

Alex.

[Douglas J. Steele]

Re: Adding a function/module to Access 2000

This is all I can see of this thread, so I don't know whether it's been
covered elsewhere, but you're using fConcatFld("PtN otes") in your query, and
the code in the URL you mention requires 5 arguments.

--
Doug Steele, Microsoft Access MVP

http://I.Am/DougSteele

(No private e-mails, please)



"Alex" <alex@totallyne rd.com> wrote in message
news:2ba4b4eb.0 404270649.35923 eda@posting.goo gle.com...[color=blue]
> "Bradley" <bradley@REMOVE THIScomcen.com. au> wrote in message[/color]
news:<4088e80a$ 1@nexus.comcen. com.au>...[color=blue][color=green]
> > Alex wrote:[color=darkred]
> > > "Bradley" <bradley@REMOVE THIScomcen.com. au> wrote in message
> > > news:<40887047$ 1@nexus.comcen. com.au>...
> > >> Alex wrote:
> > >>> Hi all,
> > >>>
> > >>> I've found a module that I think will help me combine fields
> > >>> properly, but I'm unsure how to add or use it with Access 2000.
> > >>> Below is the module I'd like to add:
> > >>>
> > >>> http://www.mvps.org/access/modules/mdl0008.htm
> > >>>
> > >>> In Access, I goto Modules and create new. Leaving 'Option Compare
> > >>> Database' on top I paste the module code in and save - naming it
> > >>> fConcatFld. It saves fine, but when I use fConcatFld in my query,
> > >>> like below:
> > >>>
> > >>> SELECT PtName, fConcatFld("PtN otes") as Combined
> > >>> FROM TESTCMSSPatient NotesQ001
> > >>> Group by PtName
> > >>>
> > >>> It errors out saying fConcatFld is an invalid function. I might
> > >>> have this entire process wrong... so how do I add this function to
> > >>> Access so I can use it?
> > >>>[/color][/color][/color]

[Bradley]

Re: Adding a function/module to Access 2000

Alex wrote:[color=blue]
> "Bradley" <bradley@REMOVE THIScomcen.com. au> wrote in message
> news:<4088e80a$ 1@nexus.comcen. com.au>...[color=green]
>> Alex wrote:[color=darkred]
>>> "Bradley" <bradley@REMOVE THIScomcen.com. au> wrote in message
>>> news:<40887047$ 1@nexus.comcen. com.au>...
>>>> Alex wrote:
>>>>> Hi all,
>>>>>
>>>>> I've found a module that I think will help me combine fields
>>>>> properly, but I'm unsure how to add or use it with Access 2000.
>>>>> Below is the module I'd like to add:
>>>>>
>>>>> http://www.mvps.org/access/modules/mdl0008.htm
>>>>>
>>>>> In Access, I goto Modules and create new. Leaving 'Option Compare
>>>>> Database' on top I paste the module code in and save - naming it
>>>>> fConcatFld. It saves fine, but when I use fConcatFld in my query,
>>>>> like below:
>>>>>
>>>>> SELECT PtName, fConcatFld("PtN otes") as Combined
>>>>> FROM TESTCMSSPatient NotesQ001
>>>>> Group by PtName
>>>>>
>>>>> It errors out saying fConcatFld is an invalid function. I might
>>>>> have this entire process wrong... so how do I add this function to
>>>>> Access so I can use it?
>>>>>
>>>>> Thanks,
>>>>>
>>>>> Alex.
>>>>
>>>> Read the page you got the code from...
>>>>
>>>> You need the function from the NorthWind database that comes as a
>>>> sample with Access.
>>>>
>>>> "Using the fConcatFld function, in the Northwind database..."
>>>>
>>>> ^^^^^^^^^^^^^^^ ^^^^^^^^
>>>>
>>>>> P
>>>
>>> Hi,
>>>
>>> The give the code to add to *any* database, but use Northwind as an
>>> example. From what I read the code isn't specific to Northwind, else
>>> what use would giving an example code with such a disclaimer in the
>>> comments be used for?
>>>
>>> Thanks for the comments...
>>>
>>> Alex[/color]
>>
>> You need to cut and paste into your database the function
>> fConcatFld() function found in the modules in the Northwind database.
>>
>> The page says it depends on a function that is in the Northwind
>> database... so you need a copy of that as well. Read the web page
>> carefully.[/color]
>
>
> Hi.
>
> I guess that's the problem... where to I paste the function into
> Access? There's no obvious place called 'Functions' where you put
> stuff like this. I tried adding it as a Module, but didn't work...
> says Unknown Function.
>
> So back to my root question... where the heck do I put the Function
> code in Access so queries will see the custom function?
>
> Thanks,
>
> Alex.[/color]

You've added the function from the web site right? Ok.

Now, you need to ALSO add the function from the Northwind database that
is referred to in the previous function (as noted on the web site).

So...

Create a new module...
Cut and paste the initial function from the web site...
Go to Northwind.mdb and cut and paste the fConcatFld() function into
your new module

--
regards,

Bradley