Re: Concatenate same field records into one.

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:

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" <Prohock@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B5352115-6D6E-4556-A44C-DE3A58C901A2@xxxxxxxxxxxxxxxx
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:

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" <Prohock@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F7DCC147-8826-4D81-832E-959975EFE14B@xxxxxxxxxxxxxxxx
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:

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" <Prohock@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A4B09333-8658-4506-8238-D77BF3655FF8@xxxxxxxxxxxxxxxx
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?

.

.

.

.

Relevant Pages

  • Re: Concatenate same field records into 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. ... Allen Browne - Microsoft MVP. ... >> Concatenate values from related records ...
    (microsoft.public.access.queries)
  • Re: Concatenate same field records into one.
    ... Concatenate the literal values into the string ... Allen Browne - Microsoft MVP. ... I have taken the SQL statement from the working Append query and I have ...
    (microsoft.public.access.queries)
  • Re: Between ( ) Function
    ... In query design view, choose Parameters on the Query menu. ... Allen Browne - Microsoft MVP. ... Tips for Access users - http://allenbrowne.com/tips.html ...
    (microsoft.public.access.queries)
  • Re: Concatenate same field records into one.
    ... Allen Browne - Microsoft MVP. ... Perth, Western Australia ... Tips for Access users - http://allenbrowne.com/tips.html ... What I prefer to do is to create the query statement dynamically. ...
    (microsoft.public.access.queries)
  • Re: Adding a NULL value
    ... Allen Browne - Microsoft MVP. ... Tips for Access users - http://allenbrowne.com/tips.html ... using the queries as inputs for different combo boxes on a form. ... then generates another query to give data to a report. ...
    (microsoft.public.access.queries)