Solved Line Breaks on Field change

[arnelgp]

change to this, then change 1 to number of spaces you want:

Public Function fnGroupBy(ByVal delimitedString As String, _
                    ByVal NumberInGroup As String, _
                    Optional ByVal delim As String = ",") As String
Dim i As Integer, j As Integer
Dim retVal As String
Dim var
var = Split(delimitedString, delim)
For i = 0 To UBound(var)
    retVal = retVal & var(i) & Space(1)     'try changing 1 to number of spaces you want
    If ((i + 1) Mod NumberInGroup) = 0 Then
        retVal = Trim$(retVal)
        retVal = retVal & vbCrLf
    End If
Next
retVal = Trim$(retVal)
fnGroupBy = retVal
End Function

 

[Charlie3]

Thanks !

 

[Charlie3]

Hello again. Things were looking good but then I realized the query is only returning the first 7 issues for each title.

For instance, in the Avengers line below, only the first 7 issues are listed but there are 125 issues in the table. Similar for Batman and all others, no more than 7 issues are ever displayed. The underlying table does have all the issues, but only the first 7 of each are displayed by the query.

Any suggestions please?

 

[Gasman]

No, arnel is just giving you blocks of 7 per line, depending on how wide your control is.
You can see his report gives you multiple lines per control.

 

[Charlie3]

What would I need to do to get all issues printed across however many rows would be needed to display them all?

 

[Gasman]

Upload your db.
There cannot be anything confidential in there, surely?

 

[Gasman]

Here is @arnelgp's version with number changed to 14

Also try the report, as I do not think the row is deep enough to show the other data due to the CR&LF ?

With rows shrunken

 

[Charlie3]

Thanks , DB is attached. Nothing confidential except maybe how bad I am at housekeeping

The table is w8A and the query is w8a_q1.

Very much appreciated !!

 

[Gasman]

You are not using what @arnelgp last posted, as you have records with no issue in them?

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

Also you have Title with ' in it, which throws off the concatenation, so change to "" instead to surround the title.
Gumby's Winter Fun Special

Like this

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,",");

BTW that is @arnelgp's query not mine. Change the 7 to as many as your control can hold widthwise.

As you can see from the picture. you are getting all the issues.?

So use the report as the definitive output.

 

[Gasman]

I would also add index for each field you are searching for?

 

[Charlie3]

Thanks, will do ! It will be a few hours before I can get back to it, will post here when I do.

 

[Charlie3]

BTW, I fixed all the records that had no issue number. Titles with " ' " in them also caused issues, I fixed those as well.

 

[Gasman]

No need to fix the titles, I amended the code to cater for them?
That is what I posted earlier?

 

[Charlie3]

Hey Gasman and arnelgp, you have both been a great help to me, thank you so much!

So ok, it’s getting there, with one problem remaining.

Please see the attached DB and explanatory PDF.

Best,
Charlie

 

[arnelgp]

i created a temporary table (ZZcmx), it is faster than
using query.
see report agpReport.
on the opening event of this report i fill ZZcmx (on calling FillZZcmx on Module2).
next i assign report.subReport to the subreport control, Child32.

i am using cmx table, since there are many tables i found in your sample db.

 

[Charlie3]

Thank you arnelgp.

agpReport returns only titles on my end and I very confused in trying to understand the processing. This is way over my head although I certainly do appreciate the work and caring that went into this.

arnelgp and Gasman, please can we revisit my last post? I've reattached that specific DB here along with the explanatory slides, please can we work from those?

We were really close with the attached DB, and this will be a limited-use tool for me. The only thing that will change from time to time is that I will update the underlying table by reloading an updated spreadsheet. The speed of the report is not a critical factor.

 

[arnelgp]

again, check and test.
i am using cmxW7a table and on main Report i am using qryCmxW7a query as recordsource.
you modify Module2 to correct table if you will use another table.
open agpReport.

 

[Charlie3]

Wow ... agpReport works now, looks great !! Sorry for my earlier reluctance.

Thanks so much, this is great!

 

[Charlie3]

Am I able to register my appreciation for your help? I'm a newbie here, I don't yet know how to do that.

 

[arnelgp]

post your actual db and i'll create the query, report, module for you.