By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,493 Members | 1,208 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,493 IT Pros & Developers. It's quick & easy.

Adding a function/module to Access 2000

P: n/a
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.
Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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.

Nov 12 '05 #2

P: n/a
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
Nov 12 '05 #3

P: n/a
"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
Nov 12 '05 #4

P: n/a
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
Nov 12 '05 #5

P: n/a
"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.
Nov 12 '05 #6

P: n/a
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?
>>

Nov 12 '05 #7

P: n/a
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
Nov 12 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.