Combining Query Results

  • Thread starter Thread starter slickdock
  • Start date Start date
S

slickdock

I have a select query that finds 4 records and has one field: [txtLastName].
Is there a way to, instead of returning 4 records, return ONE record that
puts all 4 names in one field, separated by commas?
 
Thanks so much. I'll give it a try. Since my vba skills are weak, can you
help me change the comma in the module you referenced to a hard return
between names? I know about Chr13&10, but I don't know how to use it instead
of this here. Here are the lines in the module that define it:

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String

Marshall Barton said:
slickdock said:
I have a select query that finds 4 records and has one field: [txtLastName].
Is there a way to, instead of returning 4 records, return ONE record that
puts all 4 names in one field, separated by commas?
Click to expand...


I recommend using the Concatenate function at:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16
Click to expand...
 
Don't modify the function. When you call it, use the cr/lf
in the second argument:

Concatenate("your sql statement", Chr(13) & Chr(10))
--
Marsh
MVP [MS Access]

Thanks so much. I'll give it a try. Since my vba skills are weak, can you
help me change the comma in the module you referenced to a hard return
between names? I know about Chr13&10, but I don't know how to use it instead
of this here. Here are the lines in the module that define it:

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String

Marshall Barton said:
slickdock said:
I have a select query that finds 4 records and has one field: [txtLastName].
Is there a way to, instead of returning 4 records, return ONE record that
puts all 4 names in one field, separated by commas?
Click to expand...


I recommend using the Concatenate function at:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16
Click to expand...
Click to expand...
 
Thank you so much again, Marshall. I see how that works with the sample
database. I'm afraid I still need help.

The example database is SO close, but I'm not trying to identify similar
last names and "group" the first names as this example shows. I'm just trying
to take the results of the lastname field and run them all together in 1
record with hard returns. I've tried simplifying the concatenate statement,
but since I'm so weak in vb, I'm not having success.

To add to it, I'm not actually taking LastName straight from a table. I'm
trying to take the results of a query (q_formats), which combines some ifs
and other variables to come up with a query column called FormattedLastName.
I tried building the concatenate query off of q_formats and referencing
FormattedLastName in the concatenate statement, but I bombed out there as
well.

So, its acutally FormattedLastName field from q_formats that I need to run
together with hard returns.

I appreciate any help you can give.


Marshall Barton said:
Don't modify the function. When you call it, use the cr/lf
in the second argument:

Concatenate("your sql statement", Chr(13) & Chr(10))
--
Marsh
MVP [MS Access]

Thanks so much. I'll give it a try. Since my vba skills are weak, can you
help me change the comma in the module you referenced to a hard return
between names? I know about Chr13&10, but I don't know how to use it instead
of this here. Here are the lines in the module that define it:

Function Concatenate(pstrSQL As String, _
Optional pstrDelim As String = ", ") _
As String

Marshall Barton said:
slickdock wrote:
I have a select query that finds 4 records and has one field: [txtLastName].
Is there a way to, instead of returning 4 records, return ONE record that
puts all 4 names in one field, separated by commas?


I recommend using the Concatenate function at:
http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16
Click to expand...
Click to expand...
Click to expand...
 
slickdock said:
Thank you so much again, Marshall. I see how that works with the sample
database. I'm afraid I still need help.

The example database is SO close, but I'm not trying to identify similar
last names and "group" the first names as this example shows. I'm just trying
to take the results of the lastname field and run them all together in 1
record with hard returns. I've tried simplifying the concatenate statement,
but since I'm so weak in vb, I'm not having success.

To add to it, I'm not actually taking LastName straight from a table. I'm
trying to take the results of a query (q_formats), which combines some ifs
and other variables to come up with a query column called FormattedLastName.
I tried building the concatenate query off of q_formats and referencing
FormattedLastName in the concatenate statement, but I bombed out there as
well.

So, its acutally FormattedLastName field from q_formats that I need to run
together with hard returns.
Click to expand...


You do not need to know or use any VBA to use the function
in a form or report text box. Just use it in the text box's
control source expression.

If you'll post your query's SQL statement, I'll try to show
you how to use it in the function.
 
Once again, thank you Marshall. Here is the statement:

SELECT [qryLetterCC].MatID, [qryLetterCC].FormattedLastName
FROM qryLetterCC;

It's the FormattedLastName that I'd like to combine into 1 record, separated
by hard returns.
 
Try using:
Concatenate("SELECT FormattedLastName FROM qryLetterCC",
Chr(13) & Chr(10))
--
Marsh
MVP [MS Access]

SELECT [qryLetterCC].MatID, [qryLetterCC].FormattedLastName
FROM qryLetterCC;

It's the FormattedLastName that I'd like to combine into 1 record, separated
by hard returns.

Marshall Barton said:
You do not need to know or use any VBA to use the function
in a form or report text box. Just use it in the text box's
control source expression.

If you'll post your query's SQL statement, I'll try to show
you how to use it in the function.
Click to expand...
Click to expand...
 
Thank you. It stops, and debugger highlights this line from the ADO section:

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

So since I don't know the difference between ado and dao, I did what the
module suggested and tried commenting out the ado portion and uncommenting
dao, but it didn't like that either. It stopped at the dao section.

Marshall Barton said:
Try using:
Concatenate("SELECT FormattedLastName FROM qryLetterCC",
Chr(13) & Chr(10))
--
Marsh
MVP [MS Access]

SELECT [qryLetterCC].MatID, [qryLetterCC].FormattedLastName
FROM qryLetterCC;

It's the FormattedLastName that I'd like to combine into 1 record, separated
by hard returns.

Marshall Barton said:
slickdock wrote:
Thank you so much again, Marshall. I see how that works with the sample
database. I'm afraid I still need help.

The example database is SO close, but I'm not trying to identify similar
last names and "group" the first names as this example shows. I'm just trying
to take the results of the lastname field and run them all together in 1
record with hard returns. I've tried simplifying the concatenate statement,
but since I'm so weak in vb, I'm not having success.

To add to it, I'm not actually taking LastName straight from a table. I'm
trying to take the results of a query (q_formats), which combines some ifs
and other variables to come up with a query column called FormattedLastName.
I tried building the concatenate query off of q_formats and referencing
FormattedLastName in the concatenate statement, but I bombed out there as
well.

So, its acutally FormattedLastName field from q_formats that I need to run
together with hard returns.


You do not need to know or use any VBA to use the function
in a form or report text box. Just use it in the text box's
control source expression.

If you'll post your query's SQL statement, I'll try to show
you how to use it in the function.
Click to expand...
Click to expand...
Click to expand...
 
I forgot to mention in the last reply that when it stops before the debugger,
it says: no value given for one or more required parameters, then takes me
to the ado section that I referenced in my last post.

Marshall Barton said:
Try using:
Concatenate("SELECT FormattedLastName FROM qryLetterCC",
Chr(13) & Chr(10))
--
Marsh
MVP [MS Access]

SELECT [qryLetterCC].MatID, [qryLetterCC].FormattedLastName
FROM qryLetterCC;

It's the FormattedLastName that I'd like to combine into 1 record, separated
by hard returns.

Marshall Barton said:
slickdock wrote:
Thank you so much again, Marshall. I see how that works with the sample
database. I'm afraid I still need help.

The example database is SO close, but I'm not trying to identify similar
last names and "group" the first names as this example shows. I'm just trying
to take the results of the lastname field and run them all together in 1
record with hard returns. I've tried simplifying the concatenate statement,
but since I'm so weak in vb, I'm not having success.

To add to it, I'm not actually taking LastName straight from a table. I'm
trying to take the results of a query (q_formats), which combines some ifs
and other variables to come up with a query column called FormattedLastName.
I tried building the concatenate query off of q_formats and referencing
FormattedLastName in the concatenate statement, but I bombed out there as
well.

So, its acutally FormattedLastName field from q_formats that I need to run
together with hard returns.


You do not need to know or use any VBA to use the function
in a form or report text box. Just use it in the text box's
control source expression.

If you'll post your query's SQL statement, I'll try to show
you how to use it in the function.
Click to expand...
Click to expand...
Click to expand...
 
If your problem has anything to do with ADO. I am not the
guy. I only use DAO (Jet db engin's native interface) and
when I set up the function, I just followed the comments in
the code.

Whichever interface you use, make sure you have a reference
to that library (VBA window - Tools menu). If you have a
reference to both the DAO and ADO libraries, you might(?)
have some issues so I recommend choosing one and sticking
with it until you have a decent understandimg of the other
one.
--
Marsh
MVP [MS Access]

I forgot to mention in the last reply that when it stops before the debugger,
it says: no value given for one or more required parameters, then takes me
to the ado section that I referenced in my last post.

Marshall Barton said:
Try using:
Concatenate("SELECT FormattedLastName FROM qryLetterCC",
Chr(13) & Chr(10))

SELECT [qryLetterCC].MatID, [qryLetterCC].FormattedLastName
FROM qryLetterCC;

It's the FormattedLastName that I'd like to combine into 1 record, separated
by hard returns.

:
slickdock wrote:
Thank you so much again, Marshall. I see how that works with the sample
database. I'm afraid I still need help.

The example database is SO close, but I'm not trying to identify similar
last names and "group" the first names as this example shows. I'm just trying
to take the results of the lastname field and run them all together in 1
record with hard returns. I've tried simplifying the concatenate statement,
but since I'm so weak in vb, I'm not having success.

To add to it, I'm not actually taking LastName straight from a table. I'm
trying to take the results of a query (q_formats), which combines some ifs
and other variables to come up with a query column called FormattedLastName.
I tried building the concatenate query off of q_formats and referencing
FormattedLastName in the concatenate statement, but I bombed out there as
well.

So, its acutally FormattedLastName field from q_formats that I need to run
together with hard returns.


You do not need to know or use any VBA to use the function
in a form or report text box. Just use it in the text box's
control source expression.

If you'll post your query's SQL statement, I'll try to show
you how to use it in the function.
Click to expand...
Click to expand...
Click to expand...
 
Thank you. I looked at that menu (which I've never seen before). The only
things checked are:
VB for applications
MS Access 10.0 object library
OLE automation
MS activex data objects 2.1
MS office xp web components

Immediately below the last one on this list I see unchecked:
MS DAO 3.6 object library

Is that the one I should check? And if so, what does that mean?

Marshall Barton said:
If your problem has anything to do with ADO. I am not the
guy. I only use DAO (Jet db engin's native interface) and
when I set up the function, I just followed the comments in
the code.

Whichever interface you use, make sure you have a reference
to that library (VBA window - Tools menu). If you have a
reference to both the DAO and ADO libraries, you might(?)
have some issues so I recommend choosing one and sticking
with it until you have a decent understandimg of the other
one.
--
Marsh
MVP [MS Access]

I forgot to mention in the last reply that when it stops before the debugger,
it says: no value given for one or more required parameters, then takes me
to the ado section that I referenced in my last post.

Marshall Barton said:
Try using:
Concatenate("SELECT FormattedLastName FROM qryLetterCC",
Chr(13) & Chr(10))


slickdock wrote:
SELECT [qryLetterCC].MatID, [qryLetterCC].FormattedLastName
FROM qryLetterCC;

It's the FormattedLastName that I'd like to combine into 1 record, separated
by hard returns.

:
slickdock wrote:
Thank you so much again, Marshall. I see how that works with the sample
database. I'm afraid I still need help.

The example database is SO close, but I'm not trying to identify similar
last names and "group" the first names as this example shows. I'm just trying
to take the results of the lastname field and run them all together in 1
record with hard returns. I've tried simplifying the concatenate statement,
but since I'm so weak in vb, I'm not having success.

To add to it, I'm not actually taking LastName straight from a table. I'm
trying to take the results of a query (q_formats), which combines some ifs
and other variables to come up with a query column called FormattedLastName.
I tried building the concatenate query off of q_formats and referencing
FormattedLastName in the concatenate statement, but I bombed out there as
well.

So, its acutally FormattedLastName field from q_formats that I need to run
together with hard returns.


You do not need to know or use any VBA to use the function
in a form or report text box. Just use it in the text box's
control source expression.

If you'll post your query's SQL statement, I'll try to show
you how to use it in the function.
Click to expand...
Click to expand...
Click to expand...
 
slickdock said:
Thank you. I looked at that menu (which I've never seen before). The only
things checked are:
VB for applications
MS Access 10.0 object library
OLE automation
MS activex data objects 2.1
MS office xp web components

Immediately below the last one on this list I see unchecked:
MS DAO 3.6 object library

Is that the one I should check? And if so, what does that mean?
Click to expand...


Yes, check the DAO 3.6 library.

As for the libraries:
OLE automation
MS activex data objects 2.1
MS office xp web components
uncheck the ones you are not intentially using (especially
MS activex data objects). Checking any library that you do
not understand and do not have a good reason to use can
cause problems somewhere along the road.

Any library that is checked must be properly installed on
each user's system and must be the same version as the one
you used to create your application. This can be a really
serious pain and if not done correctly can prevent your
application from running on some machines.
 
I really appreciate all your help. Since I don't understand these libraries,
and my app runs on lots of different machines at different companies, I want
to try to work around this concatenate problem another way so I don't have to
use code I don't understand.

As you suggested, I'd really like to be able to uncheck any of these
libraries that are not being used. How can I go about determining which ones
are being used?
 
slickdock said:
I really appreciate all your help. Since I don't understand these libraries,
and my app runs on lots of different machines at different companies, I want
to try to work around this concatenate problem another way so I don't have to
use code I don't understand.

As you suggested, I'd really like to be able to uncheck any of these
libraries that are not being used. How can I go about determining which ones
are being used?
Click to expand...


If you are familiar with ADO, then by all means use it.
Duane's function works fine there too. Just follow the
comments in the code. There are quite a few other functions
out there on the web that do an equivalent job, I just think
that Concatenate is the easiest one to understand and use.

To check if you need a library, make a careful note of it,
uncheck it and try to compile your project (VBA window -
Debug menu - Compile). If you get no compile errors, then
most likely you don't need it and should leave it unchecked.
If you do get compile errors, check each one to be sure you
understand why the error occurs. It is very important to
have a good reason to check a libary and never check one
that you do not absolutely have to have. It is well worth
your time to get this stuff straightened out now instead of
running into stange problems on user's machines.

I am going to be out of touch for the next couple of weeks,
so if you need further help, start a new thread with a
summary of where things currrently stand.
 
Thank you so much for all your help Marshall!

Marshall Barton said:
If you are familiar with ADO, then by all means use it.
Duane's function works fine there too. Just follow the
comments in the code. There are quite a few other functions
out there on the web that do an equivalent job, I just think
that Concatenate is the easiest one to understand and use.

To check if you need a library, make a careful note of it,
uncheck it and try to compile your project (VBA window -
Debug menu - Compile). If you get no compile errors, then
most likely you don't need it and should leave it unchecked.
If you do get compile errors, check each one to be sure you
understand why the error occurs. It is very important to
have a good reason to check a libary and never check one
that you do not absolutely have to have. It is well worth
your time to get this stuff straightened out now instead of
running into stange problems on user's machines.

I am going to be out of touch for the next couple of weeks,
so if you need further help, start a new thread with a
summary of where things currrently stand.
Click to expand...
 
Back
Top