Combine several records to one

[Todd]

I read a post that talked about using the concatenate funtion to combine
several records to one in a query. It seems to be exactly what I need. I
tried entering the function following advice from a file at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane My code
doesn't work, however. Here is what I typed into my query based on
tblContacts:

Notes: Concatenate("SELECT Note FROM tblNotes WHERE [ContactID] =" &
[ContactID])

I am trying to combine all notes related to tblContacts into one field.
When I enter this info, it says "Undefined function 'Concatenate" in
expression." Any idea what I'm doing wrong?

 

[Duane Hookom]

You must create a new standard module and paste the Concatenate() function
into the module. Save the module with a name like "basConcatenate". (note
you could also import the module from the sample into your mdb). There are
lines of code in the function that can be commented out or un-commented
based on DAO or ADO.

 

[Todd]

Thank you!!!

You must create a new standard module and paste the Concatenate() function
into the module. Save the module with a name like "basConcatenate". (note
you could also import the module from the sample into your mdb). There are
lines of code in the function that can be commented out or un-commented
based on DAO or ADO.

--
Duane Hookom
MS Access MVP

I read a post that talked about using the concatenate funtion to combine
several records to one in a query. It seems to be exactly what I need. I
tried entering the function following advice from a file at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane My code
doesn't work, however. Here is what I typed into my query based on
tblContacts:

Notes: Concatenate("SELECT Note FROM tblNotes WHERE [ContactID] =" &
[ContactID])

I am trying to combine all notes related to tblContacts into one field.
When I enter this info, it says "Undefined function 'Concatenate" in
expression." Any idea what I'm doing wrong?

 

[Todd]

That change worked perfectly except for one thing. The output will only
display a certain number of characters. Some of the notes I am combining
are quite lengthy. They are cut off at a certain point. Is there a way to
increase the maximum characters it will hold?

You must create a new standard module and paste the Concatenate() function
into the module. Save the module with a name like "basConcatenate". (note
you could also import the module from the sample into your mdb). There are
lines of code in the function that can be commented out or un-commented
based on DAO or ADO.

--
Duane Hookom
MS Access MVP

I read a post that talked about using the concatenate funtion to combine
several records to one in a query. It seems to be exactly what I need. I
tried entering the function following advice from a file at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane My code
doesn't work, however. Here is what I typed into my query based on
tblContacts:

Notes: Concatenate("SELECT Note FROM tblNotes WHERE [ContactID] =" &
[ContactID])

I am trying to combine all notes related to tblContacts into one field.
When I enter this info, it says "Undefined function 'Concatenate" in
expression." Any idea what I'm doing wrong?

 

[Duane Hookom]

The code should return more that a certain number of characters. I don't
know your SQL so I can't tell what is causing any truncation. If you are
using phrases like "DISTINCT" or "DISTINCTROW" or "GROUP BY" then you will
probably limit the output in your query to 255 characters.

--
Duane Hookom
MS Access MVP

That change worked perfectly except for one thing. The output will only
display a certain number of characters. Some of the notes I am combining
are quite lengthy. They are cut off at a certain point. Is there a way to
increase the maximum characters it will hold?

You must create a new standard module and paste the Concatenate() function
into the module. Save the module with a name like "basConcatenate". (note
you could also import the module from the sample into your mdb). There are
lines of code in the function that can be commented out or un-commented
based on DAO or ADO.

need.

I

tried entering the function following advice from a file at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

My
code

doesn't work, however. Here is what I typed into my query based on
tblContacts:

Notes: Concatenate("SELECT Note FROM tblNotes WHERE [ContactID]

="

&

[ContactID])

I am trying to combine all notes related to tblContacts into one field.
When I enter this info, it says "Undefined function 'Concatenate" in
expression." Any idea what I'm doing wrong?

 

[Todd]

It is allowing far more than 255 characters per value, but much less than is
needed. I don't have any distinct, distinctrow, or groupby settings (that
I'm aware of). I don't even know how to access those settings! Sorry, I
just know enough to get myself into trouble sometimes. Any other
suggestions?

The code should return more that a certain number of characters. I don't
know your SQL so I can't tell what is causing any truncation. If you are
using phrases like "DISTINCT" or "DISTINCTROW" or "GROUP BY" then you will
probably limit the output in your query to 255 characters.

--
Duane Hookom
MS Access MVP

That change worked perfectly except for one thing. The output will only
display a certain number of characters. Some of the notes I am combining
are quite lengthy. They are cut off at a certain point. Is there a way to
increase the maximum characters it will hold?

You must create a new standard module and paste the Concatenate() function
into the module. Save the module with a name like "basConcatenate". (note
you could also import the module from the sample into your mdb). There are
lines of code in the function that can be commented out or un-commented
based on DAO or ADO.

--
Duane Hookom
MS Access MVP


I read a post that talked about using the concatenate funtion to combine
several records to one in a query. It seems to be exactly what I

need.

I
tried entering the function following advice from a file at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane My
code
doesn't work, however. Here is what I typed into my query based on
tblContacts:

Notes: Concatenate("SELECT Note FROM tblNotes WHERE [ContactID]

="

&
[ContactID])

I am trying to combine all notes related to tblContacts into one field.
When I enter this info, it says "Undefined function 'Concatenate" in
expression." Any idea what I'm doing wrong?

 

[Duane Hookom]

What do you mean by "quite lengthy"? There are limits to the number of
characters that will display in a text box.

--
Duane Hookom
MS Access MVP

It is allowing far more than 255 characters per value, but much less than is
needed. I don't have any distinct, distinctrow, or groupby settings (that
I'm aware of). I don't even know how to access those settings! Sorry, I
just know enough to get myself into trouble sometimes. Any other
suggestions?

The code should return more that a certain number of characters. I don't
know your SQL so I can't tell what is causing any truncation. If you are
using phrases like "DISTINCT" or "DISTINCTROW" or "GROUP BY" then you will
probably limit the output in your query to 255 characters.

--
Duane Hookom
MS Access MVP

That change worked perfectly except for one thing. The output will only
display a certain number of characters. Some of the notes I am combining
are quite lengthy. They are cut off at a certain point. Is there a

way
to

increase the maximum characters it will hold?





You must create a new standard module and paste the Concatenate() function
into the module. Save the module with a name like "basConcatenate". (note
you could also import the module from the sample into your mdb).

There
are

lines of code in the function that can be commented out or un-commented
based on DAO or ADO.

--
Duane Hookom
MS Access MVP


I read a post that talked about using the concatenate funtion to combine
several records to one in a query. It seems to be exactly what I need.
I
tried entering the function following advice from a file at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane My
code
doesn't work, however. Here is what I typed into my query based on
tblContacts:

Notes: Concatenate("SELECT Note FROM tblNotes WHERE [ContactID] ="
&
[ContactID])

I am trying to combine all notes related to tblContacts into one field.
When I enter this info, it says "Undefined function 'Concatenate" in
expression." Any idea what I'm doing wrong?

 

[Todd]

There doesn't seem to be much of a pattern for where the notes are
truncated. One record's value was truncated after 826 characters (including
spaces). Another after 644. A third after 591. One of my concatenated
notes values could easily be more than 1500 characters. Thank you for all
your help, Duane.

What do you mean by "quite lengthy"? There are limits to the number of
characters that will display in a text box.

--
Duane Hookom
MS Access MVP

It is allowing far more than 255 characters per value, but much less

than

is
needed. I don't have any distinct, distinctrow, or groupby settings (that
I'm aware of). I don't even know how to access those settings! Sorry, I
just know enough to get myself into trouble sometimes. Any other
suggestions?

The code should return more that a certain number of characters. I don't
know your SQL so I can't tell what is causing any truncation. If you are
using phrases like "DISTINCT" or "DISTINCTROW" or "GROUP BY" then you will
probably limit the output in your query to 255 characters.

--
Duane Hookom
MS Access MVP


That change worked perfectly except for one thing. The output will only
display a certain number of characters. Some of the notes I am combining
are quite lengthy. They are cut off at a certain point. Is there a way
to
increase the maximum characters it will hold?





You must create a new standard module and paste the Concatenate()
function
into the module. Save the module with a name like "basConcatenate".
(note
you could also import the module from the sample into your mdb). There
are
lines of code in the function that can be commented out or un-commented
based on DAO or ADO.

--
Duane Hookom
MS Access MVP


I read a post that talked about using the concatenate funtion to
combine
several records to one in a query. It seems to be exactly what I
need.
I
tried entering the function following advice from a file at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
My
code
doesn't work, however. Here is what I typed into my query based on
tblContacts:

Notes: Concatenate("SELECT Note FROM tblNotes WHERE [ContactID]
="
&
[ContactID])

I am trying to combine all notes related to tblContacts into one
field.
When I enter this info, it says "Undefined function

'Concatenate"

in

 

[Duane Hookom]

There is nothing in the function that would limit the number of characters.
It should always return all characters. Does this happen in the query or in
a report? Have you tried the function in the debug window? What is your
current SQL?

--
Duane Hookom
MS Access MVP

There doesn't seem to be much of a pattern for where the notes are
truncated. One record's value was truncated after 826 characters (including
spaces). Another after 644. A third after 591. One of my concatenated
notes values could easily be more than 1500 characters. Thank you for all
your help, Duane.

What do you mean by "quite lengthy"? There are limits to the number of
characters that will display in a text box.

--
Duane Hookom
MS Access MVP


than

Sorry,

I you
will will
only a
way

what

I

need.
I
tried entering the function following advice from a file at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
My
code
doesn't work, however. Here is what I typed into my query

based
on

tblContacts:

Notes: Concatenate("SELECT Note FROM tblNotes WHERE [ContactID]
="
&
[ContactID])

I am trying to combine all notes related to tblContacts into one
field.
When I enter this info, it says "Undefined function

'Concatenate"

in
expression." Any idea what I'm doing wrong?

 

[Todd]

The truncating happens in the query itself. Here is the expression I'm
using:

Notes: Concatenate("SELECT NoteDate & ' - ' & Notes FROM [Notes] WHERE
ContactID =" & [ContactID],Chr(13) & Chr(10))

Thanks again for your help...

There is nothing in the function that would limit the number of characters.
It should always return all characters. Does this happen in the query or in
a report? Have you tried the function in the debug window? What is your
current SQL?

--
Duane Hookom
MS Access MVP

There doesn't seem to be much of a pattern for where the notes are
truncated. One record's value was truncated after 826 characters (including
spaces). Another after 644. A third after 591. One of my concatenated
notes values could easily be more than 1500 characters. Thank you for all
your help, Duane.




Sorry, you
are

there

a

funtion

to

combine
several records to one in a query. It seems to be exactly

what

I
need.
I
tried entering the function following advice from a file at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
My
code
doesn't work, however. Here is what I typed into my query based
on
tblContacts:

Notes: Concatenate("SELECT Note FROM tblNotes WHERE [ContactID]
="
&
[ContactID])

I am trying to combine all notes related to tblContacts into one
field.
When I enter this info, it says "Undefined function 'Concatenate"
in
expression." Any idea what I'm doing wrong?

 

[Duane Hookom]

What is the entire SQL view? Nice use of Cr Lf in your function call.

--
Duane Hookom
Microsoft Access MVP

The truncating happens in the query itself. Here is the expression I'm
using:

Notes: Concatenate("SELECT NoteDate & ' - ' & Notes FROM [Notes] WHERE
ContactID =" & [ContactID],Chr(13) & Chr(10))

Thanks again for your help...

There is nothing in the function that would limit the number of characters.
It should always return all characters. Does this happen in the query or in
a report? Have you tried the function in the debug window? What is your
current SQL?

--
Duane Hookom
MS Access MVP

There doesn't seem to be much of a pattern for where the notes are
truncated. One record's value was truncated after 826 characters (including
spaces). Another after 644. A third after 591. One of my concatenated
notes values could easily be more than 1500 characters. Thank you for all
your help, Duane.




What do you mean by "quite lengthy"? There are limits to the number of
characters that will display in a text box.

--
Duane Hookom
MS Access MVP


It is allowing far more than 255 characters per value, but much less
than
is
needed. I don't have any distinct, distinctrow, or groupby settings
(that
I'm aware of). I don't even know how to access those settings! Sorry,
I
just know enough to get myself into trouble sometimes. Any other
suggestions?


The code should return more that a certain number of characters. I
don't
know your SQL so I can't tell what is causing any truncation. If you
are
using phrases like "DISTINCT" or "DISTINCTROW" or "GROUP BY"

then
you

will
probably limit the output in your query to 255 characters.

--
Duane Hookom
MS Access MVP


That change worked perfectly except for one thing. The output will
only
display a certain number of characters. Some of the notes I am
combining
are quite lengthy. They are cut off at a certain point. Is

there

a
way
to
increase the maximum characters it will hold?





You must create a new standard module and paste the Concatenate()
function
into the module. Save the module with a name like
"basConcatenate".
(note
you could also import the module from the sample into your mdb).
There
are
lines of code in the function that can be commented out or
un-commented
based on DAO or ADO.

--
Duane Hookom
MS Access MVP


I read a post that talked about using the concatenate

funtion

to
combine
several records to one in a query. It seems to be exactly what
I
need.
I
tried entering the function following advice from a file at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
My
code
doesn't work, however. Here is what I typed into my query based
on
tblContacts:

Notes: Concatenate("SELECT Note FROM tblNotes WHERE
[ContactID]
="
&
[ContactID])

I am trying to combine all notes related to tblContacts

into
one

field.
When I enter this info, it says "Undefined function
'Concatenate"
in
expression." Any idea what I'm doing wrong?

 

[Todd]

Here is my entire SQL view...


SELECT [Contact Info].ContactID, Concatenate("SELECT NoteDate & ' - ' &
Notes FROM [Notes] WHERE ContactID =" & [ContactID],Chr(13) & Chr(10)) AS
Notes, [Contact Info].LastName
FROM [Contact Info];

What is the entire SQL view? Nice use of Cr Lf in your function call.

--
Duane Hookom
Microsoft Access MVP

The truncating happens in the query itself. Here is the expression I'm
using:

Notes: Concatenate("SELECT NoteDate & ' - ' & Notes FROM [Notes] WHERE
ContactID =" & [ContactID],Chr(13) & Chr(10))

Thanks again for your help...

There is nothing in the function that would limit the number of characters.
It should always return all characters. Does this happen in the query

or
in

a report? Have you tried the function in the debug window? What is your
current SQL?

--
Duane Hookom
MS Access MVP


There doesn't seem to be much of a pattern for where the notes are
truncated. One record's value was truncated after 826 characters
(including
spaces). Another after 644. A third after 591. One of my concatenated
notes values could easily be more than 1500 characters. Thank you

for
all

your help, Duane.




What do you mean by "quite lengthy"? There are limits to the

number

of

characters.

I

don't
know your SQL so I can't tell what is causing any truncation.

If
you

are
using phrases like "DISTINCT" or "DISTINCTROW" or "GROUP BY" then
you
will
probably limit the output in your query to 255 characters.

--
Duane Hookom
MS Access MVP


That change worked perfectly except for one thing. The output
will
only
display a certain number of characters. Some of the notes I am
combining
are quite lengthy. They are cut off at a certain point. Is there
a
way
to
increase the maximum characters it will hold?





You must create a new standard module and paste the
Concatenate()
function
into the module. Save the module with a name like
"basConcatenate".
(note
you could also import the module from the sample into your mdb).
There
are
lines of code in the function that can be commented out or
un-commented
based on DAO or ADO.

--
Duane Hookom
MS Access MVP


I read a post that talked about using the concatenate funtion
to
combine
several records to one in a query. It seems to be exactly
what
I
need.
I
tried entering the function following advice from a file at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
My
code
doesn't work, however. Here is what I typed into my query
based
on
tblContacts:

Notes: Concatenate("SELECT Note FROM tblNotes WHERE
[ContactID]
="
&
[ContactID])

I am trying to combine all notes related to tblContacts into
one
field.
When I enter this info, it says "Undefined function
'Concatenate"
in
expression." Any idea what I'm doing wrong?

 

[Duane Hookom]

The SQL view doesn't suggest any issues. I would consider changing the
either the field or table name so that they were not both Notes. This is a
good reason why many of us use naming conventions. I don't know if this
causes your problems but nothing else raises a red flag.

--
Duane Hookom
MS Access MVP

Here is my entire SQL view...


SELECT [Contact Info].ContactID, Concatenate("SELECT NoteDate & ' - ' &
Notes FROM [Notes] WHERE ContactID =" & [ContactID],Chr(13) & Chr(10)) AS
Notes, [Contact Info].LastName
FROM [Contact Info];

What is the entire SQL view? Nice use of Cr Lf in your function call.

--
Duane Hookom
Microsoft Access MVP

The truncating happens in the query itself. Here is the expression I'm
using:

Notes: Concatenate("SELECT NoteDate & ' - ' & Notes FROM [Notes] WHERE
ContactID =" & [ContactID],Chr(13) & Chr(10))

Thanks again for your help...



There is nothing in the function that would limit the number of
characters.
It should always return all characters. Does this happen in the

query

or number much
less characters.

truncation.

If

you
are
using phrases like "DISTINCT" or "DISTINCTROW" or "GROUP BY" then
you
will
probably limit the output in your query to 255 characters.

--
Duane Hookom
MS Access MVP


That change worked perfectly except for one thing. The output
will
only
display a certain number of characters. Some of the notes

I
am

combining
are quite lengthy. They are cut off at a certain point. Is
there
a
way
to
increase the maximum characters it will hold?





You must create a new standard module and paste the
Concatenate()
function
into the module. Save the module with a name like
"basConcatenate".
(note
you could also import the module from the sample into your
mdb).
There
are
lines of code in the function that can be commented out or
un-commented
based on DAO or ADO.

--
Duane Hookom
MS Access MVP


I read a post that talked about using the concatenate
funtion
to
combine
several records to one in a query. It seems to be exactly
what
I
need.
I
tried entering the function following advice from a

file
at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
My
code
doesn't work, however. Here is what I typed into my query
based
on
tblContacts:

Notes: Concatenate("SELECT Note FROM tblNotes WHERE
[ContactID]
="
&
[ContactID])

I am trying to combine all notes related to

tblContacts
into

one
field.
When I enter this info, it says "Undefined function
'Concatenate"
in
expression." Any idea what I'm doing wrong?

 

[Todd]

Sorry about the poor naming. This was my first database. I created a copy
of the database and changed the table to tblNotes, but that didn't help. I
guess I'm just out of luck. Thank you for all your effort. God bless!
Merry CHRISTmas!

The SQL view doesn't suggest any issues. I would consider changing the
either the field or table name so that they were not both Notes. This is a
good reason why many of us use naming conventions. I don't know if this
causes your problems but nothing else raises a red flag.

--
Duane Hookom
MS Access MVP

Here is my entire SQL view...


SELECT [Contact Info].ContactID, Concatenate("SELECT NoteDate & ' - ' &
Notes FROM [Notes] WHERE ContactID =" & [ContactID],Chr(13) & Chr(10)) AS
Notes, [Contact Info].LastName
FROM [Contact Info];

What is the entire SQL view? Nice use of Cr Lf in your function call.

--
Duane Hookom
Microsoft Access MVP


The truncating happens in the query itself. Here is the expression I'm
using:

Notes: Concatenate("SELECT NoteDate & ' - ' & Notes FROM [Notes] WHERE
ContactID =" & [ContactID],Chr(13) & Chr(10))

Thanks again for your help...



There is nothing in the function that would limit the number of
characters.
It should always return all characters. Does this happen in the

query

or
in
a report? Have you tried the function in the debug window? What is your
current SQL?

--
Duane Hookom
MS Access MVP


There doesn't seem to be much of a pattern for where the notes are
truncated. One record's value was truncated after 826 characters
(including
spaces). Another after 644. A third after 591. One of my
concatenated
notes values could easily be more than 1500 characters. Thank

you
for

all
your help, Duane.




What do you mean by "quite lengthy"? There are limits to the number
of
characters that will display in a text box.

--
Duane Hookom
MS Access MVP


It is allowing far more than 255 characters per value, but much
less
than
is
needed. I don't have any distinct, distinctrow, or groupby
settings
(that
I'm aware of). I don't even know how to access those settings!
Sorry,
I
just know enough to get myself into trouble sometimes. Any other
suggestions?


The code should return more that a certain number of characters.
I
don't
know your SQL so I can't tell what is causing any

truncation.

If
you
are
using phrases like "DISTINCT" or "DISTINCTROW" or "GROUP BY"
then
you
will
probably limit the output in your query to 255 characters.

--
Duane Hookom
MS Access MVP


That change worked perfectly except for one thing. The output
will
only
display a certain number of characters. Some of the

notes

I

out

or

un-commented
based on DAO or ADO.

--
Duane Hookom
MS Access MVP


I read a post that talked about using the concatenate
funtion
to
combine
several records to one in a query. It seems to be exactly
what
I
need.
I
tried entering the function following advice from a file
at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
My
code
doesn't work, however. Here is what I typed into my query
based
on
tblContacts:

Notes: Concatenate("SELECT Note FROM tblNotes WHERE
[ContactID]
="
&
[ContactID])

I am trying to combine all notes related to tblContacts
into
one
field.
When I enter this info, it says "Undefined function
'Concatenate"
in
expression." Any idea what I'm doing wrong?