Concatenate same field records into one.

  • Thread starter Thread starter Prohock
  • Start date Start date
P

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?
 
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.
 
Thanks Allen!

I was hoping that there was an easy solution for such a simple problem.
Access should have there own Concat type function.
 
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).
 
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.
 
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 said:
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

Reply to group, rather than allenbrowne at mvps dot org.


Prohock said:
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).
Click to expand...
.
Click to expand...
 
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

Reply to group, rather than allenbrowne at mvps dot org.


Prohock said:
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 said:
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

Reply to group, rather than allenbrowne at mvps dot org.


Prohock said:
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).

:

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

Reply to group, rather than allenbrowne at mvps dot org.


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?

.
Click to expand...
.
Click to expand...
Click to expand...
 
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 said:
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

Reply to group, rather than allenbrowne at mvps dot org.


Prohock said:
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 said:
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

Reply to group, rather than allenbrowne at mvps dot org.


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).

:

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

Reply to group, rather than allenbrowne at mvps dot org.


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?

.

.
Click to expand...
Click to expand...
.
Click to expand...
 
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

Reply to group, rather than allenbrowne at mvps dot org.


Prohock said:
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 said:
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

Reply to group, rather than allenbrowne at mvps dot org.


Prohock said:
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.

:

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

Reply to group, rather than allenbrowne at mvps dot org.


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).

:

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

Reply to group, rather than allenbrowne at mvps dot org.


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?

.

.
Click to expand...
.
Click to expand...
Click to expand...
 
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 said:
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

Reply to group, rather than allenbrowne at mvps dot org.


Prohock said:
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 said:
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

Reply to group, rather than allenbrowne at mvps dot org.


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.

:

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

Reply to group, rather than allenbrowne at mvps dot org.


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).

:

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

Reply to group, rather than allenbrowne at mvps dot org.


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?

.

.

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Prohock said:
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 said:
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

Reply to group, rather than allenbrowne at mvps dot org.


Prohock said:
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.

:

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

Reply to group, rather than allenbrowne at mvps dot org.


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.

:

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

Reply to group, rather than allenbrowne at mvps dot org.


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).

:

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

Reply to group, rather than allenbrowne at mvps dot org.


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?

.

.

.
Click to expand...
.
Click to expand...
Click to expand...
 
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 said:
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

Reply to group, rather than allenbrowne at mvps dot org.


Prohock said:
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 said:
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

Reply to group, rather than allenbrowne at mvps dot org.


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.

:

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

Reply to group, rather than allenbrowne at mvps dot org.


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).

:

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

Reply to group, rather than allenbrowne at mvps dot org.


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?

.

.

.
Click to expand...
Click to expand...
.
Click to expand...
 
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

Reply to group, rather than allenbrowne at mvps dot org.


Prohock said:
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 said:
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

Reply to group, rather than allenbrowne at mvps dot org.


Prohock said:
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.

:

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

Reply to group, rather than allenbrowne at mvps dot org.


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.

:

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

Reply to group, rather than allenbrowne at mvps dot org.


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).

:

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

Reply to group, rather than allenbrowne at mvps dot org.


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?

.

.

.
Click to expand...
.
Click to expand...
Click to expand...
 
Thanks! Allen for your help

I was finally able to get everything to work.

Allen Browne said:
Use VBA to create the query statement and assign it to the SQL property of
the QueryDef.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.


Prohock said:
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 said:
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

Reply to group, rather than allenbrowne at mvps dot org.


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.

:

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

Reply to group, rather than allenbrowne at mvps dot org.


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.

:

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

Reply to group, rather than allenbrowne at mvps dot org.


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).

:

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

Reply to group, rather than allenbrowne at mvps dot org.


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?

.

.

.

.
Click to expand...
Click to expand...
.
Click to expand...
 
Back
Top