Concatenate many to one

[Hoopster]

Please help!

I have a Report that is getting it's Data from a Select Query. The
Query has five Columns named, Pass/Repair/Reject 1, Pass/Repair/Reject 2,
Pass/Repair/Reject 3, Pass/Repair/Reject 4, Pass/Repair/Reject 5 and five
other Columns named Repair/Reject Comments 1, Repair/Reject Comments 2,
Repair/Reject Comments 3, Repair/Reject Comments 4, & Repair/Reject Comments
5. The Auditor will choose from a Drop Down list Pass, Repair or Reject to
populate the Pass/Repair/Reject Columns and add an abbreviated reason in the
Repair/Reject Comments Field. What I am trying to do is for each time the
Pass/Repair/Reject Field is populated with either Repair or Reject, is read
the comment in the Repair/Reject Comments Field and display it in an Unbound
Control called Repair Comments on a Report. I have tried everything I can
possibly think of but cannot get it to work. I have downloaded and went
through Duane Hookom's example on the web but still am at a loss. I have
tried all the following and then some I can't even remember:

I have used the following in a new Column in the Query:

Repair1: Concatenate("SELECT [Repair/Reject Comments 1] FROM [Flag Audits
Query] WHERE [Pass/Repair/Reject 1]=""" & [Pass/Repair/Reject 1] & """")

Repair1: SELECT(["Repair/Reject Comments 1]" FROM "[Flag Audits Query]"
WHERE "[Pass/Repair/Reject 1] = Repair")

I have used the following as an Expression in the Control Source of the
Control Repair Comments:

Concatenate("SELECT [Repair/Reject Comments 1] FROM [Flag Audits Query]
WHERE [Pass/Repair/Reject 1]=""" & [Pass/Repair/Reject 1] & """")

=Concatenate("SELECT [Repair/Reject Comments 1] FROM [Flag Audits Query]
WHERE [Pass/Repair/Reject 1]=""" & [Pass/Repair/Reject 1] & """")

= SELECT(["Repair/Reject Comments 1]" FROM "[Flag Audits Query]" WHERE
"[Pass/Repair/Reject 1] = Repair")

= DLookup("[Repair/Reject Comments 1]", "[Flag Audits Query]",
"[Pass/Repair/Reject 1] = 'Repair'")

I have used the following in the Visual basic Code behind the Report:

varRepair1 = Concatenate ("SELECT [Repair/Reject Comments 1] FROM [Flag
Audits Query] WHERE [Pass/Repair/Reject 1]=""" & [Pass/Repair/Reject 1] &
"""")

varRepair1 = SELECT(["Repair/Reject Comments 1]" FROM "[Flag Audits Query]"
WHERE "[Pass/Repair/Reject 1] = Repair")

varRepair1 = DLookup("[Repair/Reject Comments 1]", "[Flag Audits Query]",
"[Pass/Repair/Reject 1] = 'Repair'")

 

[Jeff L]

Try this in a new column in your query:

IIF([Pass/Repair/Reject 1]<> "Pass", [Repair/Reject Comments 1], "")

You might consider at some point redoing your table a bit. You can
accomplish the same task using 3-4 fields instead of 10. Your table
would have an ID, Status, Comments, StatusDate. Use the ID field to
tie into your main table. I am suggesting that you do it this way
because now you are not limited to 5 statuses and can have as many as
you want. Just a thought.

Please help!

I have a Report that is getting it's Data from a Select Query. The
Query has five Columns named, Pass/Repair/Reject 1, Pass/Repair/Reject 2,
Pass/Repair/Reject 3, Pass/Repair/Reject 4, Pass/Repair/Reject 5 and five
other Columns named Repair/Reject Comments 1, Repair/Reject Comments 2,
Repair/Reject Comments 3, Repair/Reject Comments 4, & Repair/Reject Comments
5. The Auditor will choose from a Drop Down list Pass, Repair or Reject to
populate the Pass/Repair/Reject Columns and add an abbreviated reason in the
Repair/Reject Comments Field. What I am trying to do is for each time the
Pass/Repair/Reject Field is populated with either Repair or Reject, is read
the comment in the Repair/Reject Comments Field and display it in an Unbound
Control called Repair Comments on a Report. I have tried everything I can
possibly think of but cannot get it to work. I have downloaded and went
through Duane Hookom's example on the web but still am at a loss. I have
tried all the following and then some I can't even remember:

I have used the following in a new Column in the Query:

Repair1: Concatenate("SELECT [Repair/Reject Comments 1] FROM [Flag Audits
Query] WHERE [Pass/Repair/Reject 1]=""" & [Pass/Repair/Reject 1] & """")

Repair1: SELECT(["Repair/Reject Comments 1]" FROM "[Flag Audits Query]"
WHERE "[Pass/Repair/Reject 1] = Repair")

I have used the following as an Expression in the Control Source of the
Control Repair Comments:

Concatenate("SELECT [Repair/Reject Comments 1] FROM [Flag Audits Query]
WHERE [Pass/Repair/Reject 1]=""" & [Pass/Repair/Reject 1] & """")

=Concatenate("SELECT [Repair/Reject Comments 1] FROM [Flag Audits Query]
WHERE [Pass/Repair/Reject 1]=""" & [Pass/Repair/Reject 1] & """")

= SELECT(["Repair/Reject Comments 1]" FROM "[Flag Audits Query]" WHERE
"[Pass/Repair/Reject 1] = Repair")

= DLookup("[Repair/Reject Comments 1]", "[Flag Audits Query]",
"[Pass/Repair/Reject 1] = 'Repair'")

I have used the following in the Visual basic Code behind the Report:

varRepair1 = Concatenate ("SELECT [Repair/Reject Comments 1] FROM [Flag
Audits Query] WHERE [Pass/Repair/Reject 1]=""" & [Pass/Repair/Reject 1] &
"""")

varRepair1 = SELECT(["Repair/Reject Comments 1]" FROM "[Flag Audits Query]"
WHERE "[Pass/Repair/Reject 1] = Repair")

varRepair1 = DLookup("[Repair/Reject Comments 1]", "[Flag Audits Query]",
"[Pass/Repair/Reject 1] = 'Repair'")

 

[Hoopster]

Jeff,

I replaced <> with the = Sign and now have a Column with only those
Comments where the Pass/Repair/Reject 1 was populated with Repair. Now how do
I use the Concatenate Funtion to get them all together?

Try this in a new column in your query:

IIF([Pass/Repair/Reject 1]<> "Pass", [Repair/Reject Comments 1], "")

You might consider at some point redoing your table a bit. You can
accomplish the same task using 3-4 fields instead of 10. Your table
would have an ID, Status, Comments, StatusDate. Use the ID field to
tie into your main table. I am suggesting that you do it this way
because now you are not limited to 5 statuses and can have as many as
you want. Just a thought.

Please help!

I have a Report that is getting it's Data from a Select Query. The
Query has five Columns named, Pass/Repair/Reject 1, Pass/Repair/Reject 2,
Pass/Repair/Reject 3, Pass/Repair/Reject 4, Pass/Repair/Reject 5 and five
other Columns named Repair/Reject Comments 1, Repair/Reject Comments 2,
Repair/Reject Comments 3, Repair/Reject Comments 4, & Repair/Reject Comments
5. The Auditor will choose from a Drop Down list Pass, Repair or Reject to
populate the Pass/Repair/Reject Columns and add an abbreviated reason in the
Repair/Reject Comments Field. What I am trying to do is for each time the
Pass/Repair/Reject Field is populated with either Repair or Reject, is read
the comment in the Repair/Reject Comments Field and display it in an Unbound
Control called Repair Comments on a Report. I have tried everything I can
possibly think of but cannot get it to work. I have downloaded and went
through Duane Hookom's example on the web but still am at a loss. I have
tried all the following and then some I can't even remember:

I have used the following in a new Column in the Query:

Repair1: Concatenate("SELECT [Repair/Reject Comments 1] FROM [Flag Audits
Query] WHERE [Pass/Repair/Reject 1]=""" & [Pass/Repair/Reject 1] & """")

Repair1: SELECT(["Repair/Reject Comments 1]" FROM "[Flag Audits Query]"
WHERE "[Pass/Repair/Reject 1] = Repair")

I have used the following as an Expression in the Control Source of the
Control Repair Comments:

Concatenate("SELECT [Repair/Reject Comments 1] FROM [Flag Audits Query]
WHERE [Pass/Repair/Reject 1]=""" & [Pass/Repair/Reject 1] & """")

=Concatenate("SELECT [Repair/Reject Comments 1] FROM [Flag Audits Query]
WHERE [Pass/Repair/Reject 1]=""" & [Pass/Repair/Reject 1] & """")

= SELECT(["Repair/Reject Comments 1]" FROM "[Flag Audits Query]" WHERE
"[Pass/Repair/Reject 1] = Repair")

= DLookup("[Repair/Reject Comments 1]", "[Flag Audits Query]",
"[Pass/Repair/Reject 1] = 'Repair'")

I have used the following in the Visual basic Code behind the Report:

varRepair1 = Concatenate ("SELECT [Repair/Reject Comments 1] FROM [Flag
Audits Query] WHERE [Pass/Repair/Reject 1]=""" & [Pass/Repair/Reject 1] &
"""")

varRepair1 = SELECT(["Repair/Reject Comments 1]" FROM "[Flag Audits Query]"
WHERE "[Pass/Repair/Reject 1] = Repair")

varRepair1 = DLookup("[Repair/Reject Comments 1]", "[Flag Audits Query]",
"[Pass/Repair/Reject 1] = 'Repair'")

 

[Jeff L]

I'm not sure what you are trying to concatenate. You certainly don't
want to concatenate all your rows of comments together do you? That
wouldn't make sense because all your comments would not be for the same
item. If you are trying to concatenate two or more fields from your
table/query together, the syntax would be:
Field1 & " " & Field2 & " " Field3

Hope that helps!

Jeff,

I replaced <> with the = Sign and now have a Column with only those
Comments where the Pass/Repair/Reject 1 was populated with Repair. Now how do
I use the Concatenate Funtion to get them all together?

Try this in a new column in your query:

IIF([Pass/Repair/Reject 1]<> "Pass", [Repair/Reject Comments 1], "")

You might consider at some point redoing your table a bit. You can
accomplish the same task using 3-4 fields instead of 10. Your table
would have an ID, Status, Comments, StatusDate. Use the ID field to
tie into your main table. I am suggesting that you do it this way
because now you are not limited to 5 statuses and can have as many as
you want. Just a thought.

Please help!

I have a Report that is getting it's Data from a Select Query. The
Query has five Columns named, Pass/Repair/Reject 1, Pass/Repair/Reject 2,
Pass/Repair/Reject 3, Pass/Repair/Reject 4, Pass/Repair/Reject 5 and five
other Columns named Repair/Reject Comments 1, Repair/Reject Comments 2,
Repair/Reject Comments 3, Repair/Reject Comments 4, & Repair/Reject Comments
5. The Auditor will choose from a Drop Down list Pass, Repair or Reject to
populate the Pass/Repair/Reject Columns and add an abbreviated reason in the
Repair/Reject Comments Field. What I am trying to do is for each time the
Pass/Repair/Reject Field is populated with either Repair or Reject, is read
the comment in the Repair/Reject Comments Field and display it in an Unbound
Control called Repair Comments on a Report. I have tried everything I can
possibly think of but cannot get it to work. I have downloaded and went
through Duane Hookom's example on the web but still am at a loss. I have
tried all the following and then some I can't even remember:

I have used the following in a new Column in the Query:

Repair1: Concatenate("SELECT [Repair/Reject Comments 1] FROM [Flag Audits
Query] WHERE [Pass/Repair/Reject 1]=""" & [Pass/Repair/Reject 1] & """")

Repair1: SELECT(["Repair/Reject Comments 1]" FROM "[Flag Audits Query]"
WHERE "[Pass/Repair/Reject 1] = Repair")

I have used the following as an Expression in the Control Source of the
Control Repair Comments:

Concatenate("SELECT [Repair/Reject Comments 1] FROM [Flag Audits Query]
WHERE [Pass/Repair/Reject 1]=""" & [Pass/Repair/Reject 1] & """")

=Concatenate("SELECT [Repair/Reject Comments 1] FROM [Flag Audits Query]
WHERE [Pass/Repair/Reject 1]=""" & [Pass/Repair/Reject 1] & """")

= SELECT(["Repair/Reject Comments 1]" FROM "[Flag Audits Query]" WHERE
"[Pass/Repair/Reject 1] = Repair")

= DLookup("[Repair/Reject Comments 1]", "[Flag Audits Query]",
"[Pass/Repair/Reject 1] = 'Repair'")

I have used the following in the Visual basic Code behind the Report:

varRepair1 = Concatenate ("SELECT [Repair/Reject Comments 1] FROM [Flag
Audits Query] WHERE [Pass/Repair/Reject 1]=""" & [Pass/Repair/Reject 1] &
"""")

varRepair1 = SELECT(["Repair/Reject Comments 1]" FROM "[Flag Audits Query]"
WHERE "[Pass/Repair/Reject 1] = Repair")

varRepair1 = DLookup("[Repair/Reject Comments 1]", "[Flag Audits Query]",
"[Pass/Repair/Reject 1] = 'Repair'")

 

[Jeff L]

Yes it is possible. Probably the easiest thing to do would be to make
a function and use that to concatenate your comments. You will need to
create a module and in it put:

Public Function Concatenate() as String

Dim rst as Object, qry as String, CommentString as String

qry = "Select Comments from YourQueryName where Comments is not null;"
Set rst = Currentdb.OpenRecordset(qry)

If rst.eof = false then
rst.MoveFirst
End IF

Do While rst.eof = false

If CommentString = "" then
CommentString = rst!Comments
Else
CommentString = CommentString & ", " & rst!Comments
End If

rst!MoveNext

Concatenate = CommentString

Loop

End Function

You may need to tweak this a little bit to get it to work properly (ie
field names, query name, etc).
Now in your report, make a text box that has =Concatentate() in the
control source.

Hope that helps!

Jeff,
Well let me see if this explaines it. We are Auditing Flags. In any
given week, we could Audit maybe 25 Flags of one kind. On a Report I show the
Flag type and the number of Flags that was Passed, Repaired or Rejected for a
given week. Out of these, there may have been two or three Flags that were
sent for Repair and two or three Flags that were Rejected. When the Audit
takes place, the Auditor will add a short Comment as to why the Flag wasn't a
pass. Lets say there were two Flags that went for repair. The Auditor would
then add two Comments like Cut Crooked for one and maybe Fly Hem Out for the
second. I want to show at the bottom of the Report in a Text box both
Comments like this: Cut Crooked, Fly Hem Out. On the Report this would alow
the Reader to see the reasons why the two repairs happened. Is this possible?

I'm not sure what you are trying to concatenate. You certainly don't
want to concatenate all your rows of comments together do you? That
wouldn't make sense because all your comments would not be for the same
item. If you are trying to concatenate two or more fields from your
table/query together, the syntax would be:
Field1 & " " & Field2 & " " Field3

Hope that helps!

Jeff,

I replaced <> with the = Sign and now have a Column with only those
Comments where the Pass/Repair/Reject 1 was populated with Repair. Now how do
I use the Concatenate Funtion to get them all together?

:

Try this in a new column in your query:

IIF([Pass/Repair/Reject 1]<> "Pass", [Repair/Reject Comments 1], "")

You might consider at some point redoing your table a bit. You can
accomplish the same task using 3-4 fields instead of 10. Your table
would have an ID, Status, Comments, StatusDate. Use the ID field to
tie into your main table. I am suggesting that you do it this way
because now you are not limited to 5 statuses and can have as many as
you want. Just a thought.



Hoopster wrote:
Please help!

I have a Report that is getting it's Data from a Select Query. The
Query has five Columns named, Pass/Repair/Reject 1, Pass/Repair/Reject 2,
Pass/Repair/Reject 3, Pass/Repair/Reject 4, Pass/Repair/Reject 5 and five
other Columns named Repair/Reject Comments 1, Repair/Reject Comments 2,
Repair/Reject Comments 3, Repair/Reject Comments 4, & Repair/Reject Comments
5. The Auditor will choose from a Drop Down list Pass, Repair or Reject to
populate the Pass/Repair/Reject Columns and add an abbreviated reason in the
Repair/Reject Comments Field. What I am trying to do is for each time the
Pass/Repair/Reject Field is populated with either Repair or Reject, is read
the comment in the Repair/Reject Comments Field and display it in an Unbound
Control called Repair Comments on a Report. I have tried everything I can
possibly think of but cannot get it to work. I have downloaded and went
through Duane Hookom's example on the web but still am at a loss. I have
tried all the following and then some I can't even remember:

I have used the following in a new Column in the Query:

Repair1: Concatenate("SELECT [Repair/Reject Comments 1] FROM [Flag Audits
Query] WHERE [Pass/Repair/Reject 1]=""" & [Pass/Repair/Reject 1] & """")

Repair1: SELECT(["Repair/Reject Comments 1]" FROM "[Flag Audits Query]"
WHERE "[Pass/Repair/Reject 1] = Repair")

I have used the following as an Expression in the Control Source of the
Control Repair Comments:

Concatenate("SELECT [Repair/Reject Comments 1] FROM [Flag Audits Query]
WHERE [Pass/Repair/Reject 1]=""" & [Pass/Repair/Reject 1] & """")

=Concatenate("SELECT [Repair/Reject Comments 1] FROM [Flag Audits Query]
WHERE [Pass/Repair/Reject 1]=""" & [Pass/Repair/Reject 1] & """")

= SELECT(["Repair/Reject Comments 1]" FROM "[Flag Audits Query]" WHERE
"[Pass/Repair/Reject 1] = Repair")

= DLookup("[Repair/Reject Comments 1]", "[Flag Audits Query]",
"[Pass/Repair/Reject 1] = 'Repair'")

I have used the following in the Visual basic Code behind the Report:

varRepair1 = Concatenate ("SELECT [Repair/Reject Comments 1] FROM [Flag
Audits Query] WHERE [Pass/Repair/Reject 1]=""" & [Pass/Repair/Reject 1] &
"""")

varRepair1 = SELECT(["Repair/Reject Comments 1]" FROM "[Flag Audits Query]"
WHERE "[Pass/Repair/Reject 1] = Repair")

varRepair1 = DLookup("[Repair/Reject Comments 1]", "[Flag Audits Query]",
"[Pass/Repair/Reject 1] = 'Repair'")

 

[Duane Hookom]

There is a generic concatenate function with sample usage at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

--
Duane Hookom
MS Access MVP

Jeff,
Well let me see if this explaines it. We are Auditing Flags. In any
given week, we could Audit maybe 25 Flags of one kind. On a Report I show
the
Flag type and the number of Flags that was Passed, Repaired or Rejected
for a
given week. Out of these, there may have been two or three Flags that were
sent for Repair and two or three Flags that were Rejected. When the Audit
takes place, the Auditor will add a short Comment as to why the Flag
wasn't a
pass. Lets say there were two Flags that went for repair. The Auditor
would
then add two Comments like Cut Crooked for one and maybe Fly Hem Out for
the
second. I want to show at the bottom of the Report in a Text box both
Comments like this: Cut Crooked, Fly Hem Out. On the Report this would
alow
the Reader to see the reasons why the two repairs happened. Is this
possible?

I'm not sure what you are trying to concatenate. You certainly don't
want to concatenate all your rows of comments together do you? That
wouldn't make sense because all your comments would not be for the same
item. If you are trying to concatenate two or more fields from your
table/query together, the syntax would be:
Field1 & " " & Field2 & " " Field3

Hope that helps!

Jeff,

I replaced <> with the = Sign and now have a Column with only
those
Comments where the Pass/Repair/Reject 1 was populated with Repair. Now
how do
I use the Concatenate Funtion to get them all together?

:

Try this in a new column in your query:

IIF([Pass/Repair/Reject 1]<> "Pass", [Repair/Reject Comments 1], "")

You might consider at some point redoing your table a bit. You can
accomplish the same task using 3-4 fields instead of 10. Your table
would have an ID, Status, Comments, StatusDate. Use the ID field to
tie into your main table. I am suggesting that you do it this way
because now you are not limited to 5 statuses and can have as many as
you want. Just a thought.



Hoopster wrote:
Please help!

I have a Report that is getting it's Data from a Select Query.
The
Query has five Columns named, Pass/Repair/Reject 1,
Pass/Repair/Reject 2,
Pass/Repair/Reject 3, Pass/Repair/Reject 4, Pass/Repair/Reject 5
and five
other Columns named Repair/Reject Comments 1, Repair/Reject
Comments 2,
Repair/Reject Comments 3, Repair/Reject Comments 4, & Repair/Reject
Comments
5. The Auditor will choose from a Drop Down list Pass, Repair or
Reject to
populate the Pass/Repair/Reject Columns and add an abbreviated
reason in the
Repair/Reject Comments Field. What I am trying to do is for each
time the
Pass/Repair/Reject Field is populated with either Repair or Reject,
is read
the comment in the Repair/Reject Comments Field and display it in
an Unbound
Control called Repair Comments on a Report. I have tried everything
I can
possibly think of but cannot get it to work. I have downloaded and
went
through Duane Hookom's example on the web but still am at a loss. I
have
tried all the following and then some I can't even remember:

I have used the following in a new Column in the Query:

Repair1: Concatenate("SELECT [Repair/Reject Comments 1] FROM [Flag
Audits
Query] WHERE [Pass/Repair/Reject 1]=""" & [Pass/Repair/Reject 1] &
"""")

Repair1: SELECT(["Repair/Reject Comments 1]" FROM "[Flag Audits
Query]"
WHERE "[Pass/Repair/Reject 1] = Repair")

I have used the following as an Expression in the Control Source of
the
Control Repair Comments:

Concatenate("SELECT [Repair/Reject Comments 1] FROM [Flag Audits
Query]
WHERE [Pass/Repair/Reject 1]=""" & [Pass/Repair/Reject 1] & """")

=Concatenate("SELECT [Repair/Reject Comments 1] FROM [Flag Audits
Query]
WHERE [Pass/Repair/Reject 1]=""" & [Pass/Repair/Reject 1] & """")

= SELECT(["Repair/Reject Comments 1]" FROM "[Flag Audits Query]"
WHERE
"[Pass/Repair/Reject 1] = Repair")

= DLookup("[Repair/Reject Comments 1]", "[Flag Audits Query]",
"[Pass/Repair/Reject 1] = 'Repair'")

I have used the following in the Visual basic Code behind the
Report:

varRepair1 = Concatenate ("SELECT [Repair/Reject Comments 1] FROM
[Flag
Audits Query] WHERE [Pass/Repair/Reject 1]=""" &
[Pass/Repair/Reject 1] &
"""")

varRepair1 = SELECT(["Repair/Reject Comments 1]" FROM "[Flag Audits
Query]"
WHERE "[Pass/Repair/Reject 1] = Repair")

varRepair1 = DLookup("[Repair/Reject Comments 1]", "[Flag Audits
Query]",
"[Pass/Repair/Reject 1] = 'Repair'")