Concatenate same field records into one.

[Prohock]

I have a query call QryFind that produces one field with many records. Simply
I need to Concatenate the results to display in a text box on a form.

From
A
B
C

I need

A, B, C

How do I do this?

[Jeff Boyce]

I seem to recall a "concatenate" function that Duane H. posted a while back.
Try searching on-line with those as keywords...

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Prohock" <Pro...@discussions.microsoft.com> wrote in message
news:A4B09333-8658-4506...@microsoft.com...

[Allen Browne]

This may do the job for you:
Concatenate values from related records
at:
http://allenbrowne.com/func-concat.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Prohock" <Pro...@discussions.microsoft.com> wrote in message
news:A4B09333-8658-4506...@microsoft.com...

[Prohock]

Thanks Allen!

I was hoping that there was an easy solution for such a simple problem.
Access should have there own Concat type function.

"Allen Browne" wrote:

> .
>

[Prohock]

Hi Allen

I can't get it to work! I get an error that says error 3061: Too Few
Parameters. Expected 2.

I have the following in a textbox named "grades" in a report named
"RptOutputForm" who's source is "QryReportSource".

The query that supplies the information for this specific text box is
"QryPrevious"

=ConcatRelated("Letter","QryPrevious")

I have only one field "Letter" of values in the query "QryPrevious"

A
B
C

I am unsure as to what should be imputed in for the 3rd, and 4th values of
the string (I am assuming that this is the source of the error).

"Allen Browne" wrote:

> .
>

[Allen Browne]

The request for parameters means there are 2 names in the query that Access
can't resolve.

Does the query contain names such as:
[Forms].[Form1].[Text0]
If so, those are the parameters. That won't work for in the context of the
DAO code.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Prohock" <Pro...@discussions.microsoft.com> wrote in message

news:F7DCC147-8826-4D81...@microsoft.com...

[Prohock]

Hi Allen

I have been experimenting with the ConcatRelated function

In my query "QryPrevious" , I have a combined field consisting of two fields
called "DateLength".

[Startdate] &" - "& [SuspLength]

Startdate is a date field from a table.

The results of QryPrevious are

12/2/2009 - 3 day
12/4/2009 - 2 day
12/8/2009 - 3 day

I would to combine them into a single field, called [Alldates]. Like the
following

12/2/2009 - 3 day, 12/4/2009 - 2 day, 12/8/2009 - 3 day

What can I do with the [Startdate] &" - "& [SuspLength] so it becomes TEXT
so that the ConcatRelated can resolve it as a single value?

Like it works with

A
B
C

to A,B,C

Thanks for looking at this.

"Allen Browne" wrote:

> .
>

[Allen Browne]

It should be fine if:
a) [Startdate] and [SuspLength] are fields in the source table,

b) You have an alias name in the Field row in front of the expression, e.g.:
Expr1: [Startdate] &" - "& [SuspLength]

c) You don't have any criteria under this calculated field, nor criteria
under any further expressions that use this one.

Perhaps you can temporarily remove some things from the query until you get
the function returning results, and then start putting them back as a way of
pinning down where the problem is.

To answer your specific question you could use Str() to force the type, but
I would not have thought that was necessary:
Expr1: Str([Startdate] &" - "& [SuspLength])

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Prohock" <Pro...@discussions.microsoft.com> wrote in message

news:B5352115-6D6E-4556...@microsoft.com...

[Prohock]

Hi Allen

Thank you for getting back to me, and again I apologize for how this has
been communicated. Originaly, I thought that all I needed was a simple Access
function to do the trick (I am not a programer). So intially, I gave a
simplistic example to help clarify what I needed. I also tried to problem
solve this on my own and posted another related question that I thought would
help solve the issue. Little did I know that I would require a complex
module.

I think that C) is the problem here for me. Here is my SQL:

From PrevSuspConcat

SELECT ConcatRelated("[DateLength]","[QryPrevious]") AS PreSuspList;

From QryPrevious

SELECT QryReportSource.SuspLength, QryReportSource.StartDate,
QryReportSource.IDnumber, CStr([StartDate] & " - " & [SuspLength]) AS
DateLength
FROM QryReportSource
WHERE (((QryReportSource.StartDate) Between
[forms]![frmSuspensions]![StartDate]-1 And
[forms]![frmSuspensions]![StartDate]-305) AND
((QryReportSource.IDnumber)=[forms]![frmSuspensions]![IDNumber]));

If I get rid of the WHERE stuff, everything works perfectly ... so it is not
a combined field issue or a Date/Text issue like I had thought.

Unfortunately, I need the filtters (WHERE) from the current record in the
form to provide the right data. But some how these filters prevent the
function from working.

Do know if there is a possible work around to this delema?

I have tried to query just the one field [DateLength] and then use your
function on that but this also didn't work, I get the same error.

I tried to put the values that I needed into a table using an update query,
but I could get the query to update it always comes back zero rows.

"Allen Browne" wrote:

> .
>

[Allen Browne]

There you go: you do have parameters like:

[forms]![frmSuspensions]![StartDate]

in your lower level query.

What I prefer to do is to create the query statement dynamically.
If necessary, you can then assign the literal dates to the SQL property of a
QueryDef that you use for the purpose, e.g.:
strSql = "SELECT ... WHERE SomeDate Between #1/1/2010# And #1/31#2010#;"
CurrentDb.QueryDefs("Query1").SQL = strSql

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Prohock" <Pro...@discussions.microsoft.com> wrote in message

news:8711FDD6-1D69-439F...@microsoft.com...

[Prohock]

Is the dynamic query statment writen in the query or VBA? Can the literal
dates be dynamic based on the current data of a form? Sorry I am not familiar
with dynamic queries.

"Allen Browne" wrote:

> .
>

[Allen Browne]

Use VBA to create the query statement and assign it to the SQL property of
the QueryDef.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Prohock" <Pro...@discussions.microsoft.com> wrote in message

news:A5E49330-2B61-42A5...@microsoft.com...

[Prohock]

I have managed to make an Append query that will update an empty field in a
table and the Concat function will work properly off the table data.

So I can get everything to work manually. I would like to make it automatic.

I have taken the SQL statement from the working Append query and I have
tried to make the statement that you have suggested. This is what I have so
far, but it does not work. It is in the button to open a report. I get error
number 13 type mismatch error.

Dim strSql As String

DoCmd.SetWarnings False
strSql = "INSERT INTO TbA (DateLength) SELECT CStr([StartDate] & " - " &

[SuspLength]) AS DateLength FROM QryReportSource WHERE
(((QryReportSource.StartDate) Between [forms]![frmSuspensions]![StartDate]-1
And [forms]![frmSuspensions]![StartDate]-305) AND
((QryReportSource.IDnumber)=[forms]![frmSuspensions]![IDNumber]));"

DoCmd.RunSQL strSql

"Allen Browne" wrote:

> .
>

[Allen Browne]

Suggestions:

a) Concatenate the literal values into the string (as in the example below.)

b) Double up the quote marks inside the string:
http://allenbrowne.com/casu-17.html

c) Format the literal date as expected by JET.
(Do not change this to suit your regional settings):
http://allenbrowne.com/ser-36.html

d) Use Debug.Print to see if the string is correct. After running it, open
the Immediate Window (Ctrl+G) to see what came out.

e) Use Execute rather than RunSQL, because you know the results:
http://allenbrowne.com/ser-60.html

This kind of thing:

Dim db As DAO.Database
Dim frm As Form
Dim strSql As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"

set frm = Forms.frmSuspensions
set db = CurrentDb()
If IsDate(frm!StartDate) And IsNumeric(frm!IDNumber) Then

strSql = "INSERT INTO TbA (DateLength) " & _
"SELECT CStr([StartDate] & "" - "" & [SuspLength]) AS DateLength " & _
"FROM QryReportSource " & _
"WHERE (QryReportSource.StartDate Between " & _
Format(frm![StartDate]-1, strcJetDate) & " And " & _
Format(frm![StartDate]-305), strcJetDate) & _
") AND (QryReportSource.IDnumber =" & frm![IDNumber] & ");"

Debug.Print strSql
db.Execute strSql, dbFailOnError
Debug.Print db.RecordsAffected & " record(s) appended."
Else
MsgBox "Enter a date and a number."
End If
Set db = Nothing
set frm = Nothing

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Prohock" <Pro...@discussions.microsoft.com> wrote in message

news:99FBBBF7-E3BD-46B8...@microsoft.com...

[Prohock]

Thanks! Allen for your help

I was finally able to get everything to work.

"Allen Browne" wrote:

> .
>