Solved Line Breaks on Field change (1 Viewer)

[Charlie3]

Hi.

I'm making a report for a comic book collection using only two fields, title (the title of the series of the comic book, i.e. Avengers) and issue (i.e., 1, 2, 3, etc, - the issue number of the comic book within the series title) .

I would like to display the title, skip a line, then show all of the issue numbers in rows (across then down).

When the issues within a title are all displayed, I would like to skip a line, print the next title, skip another line and then print all of the issues within that title, across and then down, and then keep going like that until there are no more titles.

Sample:

Avengers

103 108 121 134 154 155 156
162 167 184 189 190 191 202
206 209 211 212

Fantastic Four

111 113 114 115 117 120 121
122 123 127 129 130 134 135
137

X-Men

109 115 117 118 119 125 125
127 128 129

I'm using Access 2007

I tried using page setup/columns. That prints everything out in rows including the title.

In a nutshell, I haven't been able to get blank lines displayed around the title when it changes, the title field is mixed into columns with the issue numbers.

I've tried searching online and in the doc but haven't found an answer.

Thanks for any help you can provide.

Charlie

 

[Gasman]

Use concatenation to get all the issue numbers together.
You can use Allen Browne's function or theDBGuys.
The latter is likely the simplest version for your requirements.

Microsoft Access tips: Concatenate values from related records

Return a string containing the values of related records.

allenbrowne.com

Code Snippets

 

[Charlie3]

Thanks Gasman. I am a lightweight with this but will try. If the DBGuys function is simpler, please can you post a link to that as well?

Also, please let me restate a bit before I dive in. Without any code, I am able to get the issues to print in rows across and down. My specific problem is that the title is washed in with the issue rows. Is there some simple way to get line breaks before and after the title and not have the title washed in with the issue rows? Thanks.

 

[Gasman]

the DBGuy's is the second lnk Code Snippets.

However you might just get a away with concatenating vbCRLF or CHR(10) and CHR(13) to the title and before the issues?

 

[Charlie3]

Thanks Gasman, much appreciated. Are you able to show me an example of how to concatenate vbCRLF or CHR(10) and CHR(13) to the title and before the issues? IS that done in the Design Report view? Thanks, I'm really in over my head here.

 

[Gasman]

Show us a picture of what you have now in the report as close as you can get to what you want.

 

[Charlie3]

Please see attached, thanks !

 

[arnelgp]

see Query1 and the the 2 modules that transform your Issue.
open Query1 Report.

 

[Charlie3]

Thanks ! The query is enough for me. Do I still need the modules to make that work?

 

[arnelgp]

. Do I still need the modules to make that work?

you have to include them, since they are Called from the Query.

 

[Charlie3]

Thanks. How do I include them? (I am over my head) Thanks !

 

[arnelgp]

goto VBA window (Ctrl-G)
on the menu, Insert->Module.
then Copy and paste the two function (ConcatRelated and fnGroupBy) from my demo db to
your Module.

 

[Charlie3]

Ok, thanks I did that. Now I am getting this error when I run the query:

Here is how I modified your query - I just changed Table1 to cmx (cmx is the name of my table)

SELECT cmx.Title, fnGroupBy(ConcatRelated("Issue","cmx","Title = '" & [Title] & "'","",","),7,",") AS Expr1
FROM cmx
GROUP BY cmx.Title, fnGroupBy(ConcatRelated("Issue","cmx","Title = '" & [Title] & "'","",","),7,",");


Here's your original query for reference:

SELECT Table1.Title, fnGroupBy(ConcatRelated("Issue","Table1","Title = '" & [Title] & "'","",","),7,",") AS Expr1
FROM Table1
GROUP BY Table1.Title, fnGroupBy(ConcatRelated("Issue","Table1","Title = '" & [Title] & "'","",","),7,",");


Sorry to keep coming back with more questions.

 

[Gasman]

Show the structure of your table. Is issue numeric or text?

 

[Charlie3]

Title and Issue are both short text. But wait, see below the image ....

I brought your sample Table1 over to my Access db and your query unedited runs fine there. I think that tells me that I brought the two modules over ok and that the issue may be within my cmx table.

Any thoughts appreciated. In the meantime, I'm going to try it on a subset of my cmx table - there may be a problem with some of the issue entyries (i.e., blank or containing a " character).

 

[Gasman]

Well @arnelgp 's issue was numeric, as I would expect it to be?
If it is text, then you need to surround with single quotes.
Why is qty short text?

Get your data structure correct first, else you are going to have lots of problems.?
You cannot add text!, well not easily?

You do not need to add dummy fields either. If you decide you need more, it is easy enough to add more, with the correct data type.

 

[Charlie3]

Yayyy !!! The query runs on a subset of my table. So yes, looks like I have a data problem.

The issue field is a text field because the issue designations are not always numeric (i.e., Annual #1, King Size Special #1, etc.)

qty should be numeric, thanks.

fyi, the access db was an external data load of an excel spreadsheet that I had been using previously, I had let Access choose the table field types.

Not understanding the reference to "@arnelgp 's issue"

Thanks so much Gasman, you are a great help !! - Charlie

 

[arnelgp]

you Exclude those that has No Issue (blank):

SELECT cmx.Title, fnGroupBy(ConcatRelated("Issue","cmx","Title = '" & [Title] & "' And IsNull(Issue)=False","Issue",","),7,",") AS Expr1
FROM cmx
GROUP BY cmx.Title, fnGroupBy(ConcatRelated("Issue","cmx","Title = '" & [Title] & "' And IsNull(Issue)=False","Issue",","),7,",");

 

[Gasman]

Ok, it is something else altogether.
I have changed arnel's table to text and that still works fine?

Best to upload your DB? Compacted and if too large, zipped.

 

[Charlie3]

Ah, the modules and queries came from arnelgp, I hadn't realized. Thanks much to you too arnelgp!

I do have one more question, how do I increase the spacing between the issue numbers on the query results?

I tried adjusting the spaces between quotes and seem to have messed things up.

Thanks.