Combine field information into one textbox 1

[MMSMSD]

I have a report that contains a sub-report displaying totals of numbers based on an underlying query. In each individual record of the query, there is a short decription. What I'm looking to do is to have all those descriptions put together as one text box on the same report. Here's an example:

Table1 fields

Number1
Number2
Number3
Description
Date

A datasheet of the table would look like this:

Number1 | Number2 | Number3 | Description | Date

4 | 3 | 6 | Outside | 5/1/2006
2 | 2 | 7 | Inside | 5/1/2006
9 | 8 | 5 | Both | 5/1/2006

The query (query1) for this report merely filters out the records I need, based on the date. The subreport points directly to the query as the control source. My report needs to have totals and a single line for all descriptions, like this:

Number1 - 15
Number2 - 13
Number3 - 18
Descriptions - Outside, Inside, Both

There is a Concatenate module within TekTips (I can't find the URL now, of course!)that I've been trying but I haven't been able to get it to work. I either get "#NAME?" or "#Error" in the text box. The errors are 'run-time error 3078' or 'run-time error 3061' or 'too few parameters Expected 1'.

The best result I was able to get from the module was the 3078 error, where the error went on to say "...Jet engine could not find the entry 'Outside'...", which was the first result in that field. My syntax in the ControlSource of the textbox at that point was:

=Concatenate([Description])

I have a feeling that I'm just missing a pointer somewhere, as in I need to more specifically specify a path to the field, but nothing I've tried works. Any help is much appreciated. Thanks.

 

[dhookom]

What was your exact attempt at using the Concatenate() function? I would expect a control source something like:

=Concatenate("SELECT [Description] FROM [Table1] WHERE ...")

Make sure the name of the text box is not the name of a field in the record source of the report.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[VicRauch]

The only way I know of to concatenate the description field from several separate rows of data would be within a VBA routine. You would have to create a RecordSet, then as you read each record, concatenate it's Description field to the end of your CombinedDescription field.

I have found, that if you explain what and why you need a report that seems so strange, someone here usually has a very simple way of solving the situation. Right now, the only information we have to work with is trying to help you code the solution you have developed yourself. If we had the big picture, it is normally easier to brain storm with you for a solution.

HTH,
Vic

 

[dhookom]

I expect MMSMSD is attempting to use the generic concatenate function from faq701-4233.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MMSMSD]

dhookom,
You are correct. Thanks for tracking that down. I have it set for DAO. When I hit DEBUG, it fails on:

Set rs = db.OpenRecordset(pstrSQL)

I saw the example that's part of the module (=Concatenate("SELECT [Description] FROM [Table1] WHERE...) but I'm not trying to filter the results through the module. It's already filtered through the query. I just need it to put the text together. Do I need to filter it via the original table anyway? It's my first time using a module like this, so pardon my ignorance.

As for the report itself, it's one that we use on paper primarily, which is why it's not set with individual 'description' fields like a database would spit out. I'm making these changes to the new database to keep the format of the final report the same. I'd love to change the format and forget about this but that's not my call. I won't be able to mess with it again today but wanted to clarify, in case it sparks any other ideas. Thanks!

 

[dhookom]

MMSMSD,
"It's already filtered through the query" doesn't apply a filter to the values returned from the Concatenate() function. I expect you don't want to return every [Description] from [table1]. The Where clause in the SQL syntax is used to filter the Descriptions returned by the function. Nothing you have provided gives a clue as to which Descriptions you want returned in the function.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MMSMSD]

I have a form that allows me to enter the final date for the report before the report opens. Table1 also has a SUBMITTED flag that is checked YES/NO (1/0). So it needs to look for all dates that equal or are less than the date entered AND show only the ones that have NOT been submitted yet. Would the syntax would be something like this?:

=Concatenate("SELECT [Description] FROM [Table1] WHERE [Date] <= [FORM]![Generate]![EndDate] AND [Submitted] = 0")

Does that look about right? I'm not near the machine with the database today, so I can't try it out until tomorrow. I really appreciate the help.

 

[dhookom]

You seem to have the idea. Try something like:

=Concatenate("SELECT [Description] FROM [Table1] WHERE [Date] <= #" & [FORM]![Generate]![EndDate] & "# AND [Submitted] = 0")

You can test this by opening the Immediate Window (press Ctrl+G)

? Concatenate("SELECT [Description] FROM [Table1] WHERE [Date] <= #05-31-2006# AND [Submitted] = 0")

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[MMSMSD]

dhookom,
Your sample worked, almost as written. I ran the test and it worked without a hitch. When I changed the WHERE section from a hard-coded date to the form field, it balked at me (asking for an entry for 'form'). The final answer came by adding a space between the "#" and the " symbols on either side of the field name(go figure). So, in case anyone else needs this info:

Your sample code:
=Concatenate("SELECT [Description] FROM [Table1] WHERE [Date] <= #" & [FORM]![Generate]![EndDate] & "# AND [Submitted] = 0")

Final working code:
=Concatenate("SELECT [Description] FROM [Table1] WHERE [Date] <= # " &[FORM]![Generate]![EndDate] & " # AND [Submitted] = 0")

I know that something like a space would have made me tear my hair out(I found it only by trial and error) so I thought it best to clarify for others. Thank you very much for your time and efforts with this issue.

 

[dhookom]

I would have never expected the first to fail and the second to work. Adding the spaces should break it. Oh, well....

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]