Combining Text Field Issue

  • Thread starter Thread starter Cy
  • Start date Start date
C

Cy

First off, I hope I am posting this to the right group.

Here's what I got.

I have a table that contains a type of service, cost and a comment. I
have created a query, in which I want to combine two types of service
to get a total on one line. Easy enough. The hard situation is
trying to combine the comments.

Example, in the table might be something like this:

Type of Service Cost Comment
TV Repair $15.00 Needs soldered
TV Repair $22.00 shouldn't take more then 10 minutes
Scrub Floors $50.00 bring a new mop

In the 1 query, I am using the criteria for tv repair and scrub
floors. In a second query, I am using the prior results query to the
do a sum on the cost field. The problem is that i would like to
combine all the comments for the items into one long comment.

Final result would look something like this
Cost Comment
$87.00 needs soldered, shouldn't take more then 10 minutes, bring a
new mop (NOTE: all this in one row, combined).

So, the question is there a way to "sum" the comment field to get it
to combine or total up in one field?

Any thoughts, suggestions or help, would be appreciated. Maybe I'm
going about this all wrong, but I'm hoping someone can help me out a
tad.

Thanks in advance.
 
Did you add the function to your MDB? What is the exact expression/SQL you
used? What are the data types of significant fields?
 
I did add the function to the database. Data types are text.
Comments: Concatenate("SELECT contracttype FROM contractsdetail WHERE
contractno =" & [contractno])

Thanks
 
If the field is text then you must delimit it like:
Comments: Concatenate("SELECT contracttype FROM contractsdetail WHERE
contractno =""" & [contractno] & """")

--
Duane Hookom
MS Access MVP

Cy said:
I did add the function to the database. Data types are text.
Comments: Concatenate("SELECT contracttype FROM contractsdetail WHERE
contractno =" & [contractno])

Thanks

Did you add the function to your MDB? What is the exact expression/SQL
you
used? What are the data types of significant fields?
Click to expand...
Click to expand...
 
Duane,

Thanks a bunch. I can't believe I missed that one, but it works
great.

Very nice piece of code. Thanks a bunch.

Ron

If the field is text then you must delimit it like:
Comments: Concatenate("SELECT contracttype FROM contractsdetail WHERE
contractno =""" & [contractno] & """")

--
Duane Hookom
MS Access MVP


I did add the function to the database. Data types are text.
Comments: Concatenate("SELECT contracttype FROM contractsdetail WHERE
contractno =" & [contractno])
Thanks
Click to expand...

Did you add the function to your MDB? What is the exact expression/SQL
you
used? What are the data types of significant fields?
--
Duane Hookom
MS Access MVP

On Jun 7, 1:38 am, "Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote:
There is a generic concatenate function with sample usage
athttp://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
--
Duane Hookom
MS Access MVP

First off, I hope I am posting this to the right group.
Here's what I got.
I have a table that contains a type of service, cost and a comment.
I
have created a query, in which I want to combine two types of
service
to get a total on one line. Easy enough. The hard situation is
trying to combine the comments.
Example, in the table might be something like this:
Type of Service Cost Comment
TV Repair $15.00 Needs soldered
TV Repair $22.00 shouldn't take more then 10 minutes
Scrub Floors $50.00 bring a new mop
In the 1 query, I am using the criteria for tv repair and scrub
floors. In a second query, I am using the prior results query to
the
do a sum on the cost field. The problem is that i would like to
combine all the comments for the items into one long comment.
Final result would look something like this
Cost Comment
$87.00 needs soldered, shouldn't take more then 10 minutes, bring
a
new mop (NOTE: all this in one row, combined).
So, the question is there a way to "sum" the comment field to get it
to combine or total up in one field?
Any thoughts, suggestions or help, would be appreciated. Maybe I'm
going about this all wrong, but I'm hoping someone can help me out a
tad.
Thanks in advance.
Thanks Duane,
Tried your solution. Now I get #error in the field on the query.
Thoughts?
Click to expand...
Click to expand...
Click to expand...
 
Duane,

Thanks a bunch. I can't believe I missed that one, but it works
great.

Very nice piece of code. Thanks a bunch.

Ron

If the field is text then you must delimit it like:
Comments: Concatenate("SELECT contracttype FROM contractsdetail WHERE
contractno =""" & [contractno] & """")

--
Duane Hookom
MS Access MVP


I did add the function to the database. Data types are text.
Comments: Concatenate("SELECT contracttype FROM contractsdetail WHERE
contractno =" & [contractno])
Thanks
Click to expand...

Did you add the function to your MDB? What is the exact expression/SQL
you
used? What are the data types of significant fields?
--
Duane Hookom
MS Access MVP

On Jun 7, 1:38 am, "Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote:
There is a generic concatenate function with sample usage
athttp://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
--
Duane Hookom
MS Access MVP

First off, I hope I am posting this to the right group.
Here's what I got.
I have a table that contains a type of service, cost and a comment.
I
have created a query, in which I want to combine two types of
service
to get a total on one line. Easy enough. The hard situation is
trying to combine the comments.
Example, in the table might be something like this:
Type of Service Cost Comment
TV Repair $15.00 Needs soldered
TV Repair $22.00 shouldn't take more then 10 minutes
Scrub Floors $50.00 bring a new mop
In the 1 query, I am using the criteria for tv repair and scrub
floors. In a second query, I am using the prior results query to
the
do a sum on the cost field. The problem is that i would like to
combine all the comments for the items into one long comment.
Final result would look something like this
Cost Comment
$87.00 needs soldered, shouldn't take more then 10 minutes, bring
a
new mop (NOTE: all this in one row, combined).
So, the question is there a way to "sum" the comment field to get it
to combine or total up in one field?
Any thoughts, suggestions or help, would be appreciated. Maybe I'm
going about this all wrong, but I'm hoping someone can help me out a
tad.
Thanks in advance.
Thanks Duane,
Tried your solution. Now I get #error in the field on the query.
Thoughts?
Click to expand...
Click to expand...
Click to expand...
 
Duane,

Thanks a bunch. I can't believe I missed that one, but it works
great.

Very nice piece of code. Thanks a bunch.

Ron

If the field is text then you must delimit it like:
Comments: Concatenate("SELECT contracttype FROM contractsdetail WHERE
contractno =""" & [contractno] & """")

--
Duane Hookom
MS Access MVP


I did add the function to the database. Data types are text.
Comments: Concatenate("SELECT contracttype FROM contractsdetail WHERE
contractno =" & [contractno])
Thanks
Click to expand...

Did you add the function to your MDB? What is the exact expression/SQL
you
used? What are the data types of significant fields?
--
Duane Hookom
MS Access MVP

On Jun 7, 1:38 am, "Duane Hookom" <DuaneAtNoSpanHookomDotNet> wrote:
There is a generic concatenate function with sample usage
athttp://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.
--
Duane Hookom
MS Access MVP

First off, I hope I am posting this to the right group.
Here's what I got.
I have a table that contains a type of service, cost and a comment.
I
have created a query, in which I want to combine two types of
service
to get a total on one line. Easy enough. The hard situation is
trying to combine the comments.
Example, in the table might be something like this:
Type of Service Cost Comment
TV Repair $15.00 Needs soldered
TV Repair $22.00 shouldn't take more then 10 minutes
Scrub Floors $50.00 bring a new mop
In the 1 query, I am using the criteria for tv repair and scrub
floors. In a second query, I am using the prior results query to
the
do a sum on the cost field. The problem is that i would like to
combine all the comments for the items into one long comment.
Final result would look something like this
Cost Comment
$87.00 needs soldered, shouldn't take more then 10 minutes, bring
a
new mop (NOTE: all this in one row, combined).
So, the question is there a way to "sum" the comment field to get it
to combine or total up in one field?
Any thoughts, suggestions or help, would be appreciated. Maybe I'm
going about this all wrong, but I'm hoping someone can help me out a
tad.
Thanks in advance.
Thanks Duane,
Tried your solution. Now I get #error in the field on the query.
Thoughts?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top