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("PtNotes") as Combined
FROM TESTCMSSPatientNotesQ001
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]

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" <al**@totallynerd.com> wrote in message
news:dv********************@sysmatrix.net...

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("PtNotes") as Combined
FROM TESTCMSSPatientNotesQ001
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.

[Bradley]

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("PtNotes") as Combined
FROM TESTCMSSPatientNotesQ001
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
--
regards,

Bradley

[Alex]

"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote in message news:<40********@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("PtNotes") as Combined
FROM TESTCMSSPatientNotesQ001
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

[Bradley]

Alex wrote:

"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote in message
news:<40********@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("PtNotes") as Combined
FROM TESTCMSSPatientNotesQ001
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

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]

"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote in message news:<40********@nexus.comcen.com.au>...

Alex wrote:

"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote in message
news:<40********@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("PtNotes") as Combined
FROM TESTCMSSPatientNotesQ001
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

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.

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]

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("PtNotes") 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" <al**@totallynerd.com> wrote in message
news:2b**************************@posting.google.c om...

"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote in message

news:<40********@nexus.comcen.com.au>...

Alex wrote:

"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote in message
news:<40********@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("PtNotes") as Combined
>> FROM TESTCMSSPatientNotesQ001
>> 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?
>>

[Bradley]

Alex wrote:

"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote in message
news:<40********@nexus.comcen.com.au>...

Alex wrote:

"Bradley" <br*****@REMOVETHIScomcen.com.au> wrote in message
news:<40********@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("PtNotes") as Combined
> FROM TESTCMSSPatientNotesQ001
> 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

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.

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.

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