ConcatRelated in a Report/SubReport (Allen Browne Module)

[Ringlis1]

I am in need for using a concatrelated field in a report. The data is pulling from a seperate query. I cannot figure out how to get the results by adding a text box with the code in the ControlSource.

I created a report that correctly displays the concat as needed. However, when I add it as a subreport and link both using "ClassID" no data is displayed in the subreport and generates the following error:
Error 3075: Syntax error (missing operator) in query Expression 'ClassID='.

Here is the SQL query that I used that works:
SELECT ClassAssigned.ClassID, ConcatRelated("Name","ClassAssigned_Instructors","ClassID = " & [ClassID]) AS InstructorList
FROM ClassAssigned
GROUP BY ClassAssigned.ClassID, ConcatRelated("Name","ClassAssigned_Instructors","ClassID = " & [ClassID])
HAVING (((ClassAssigned.ClassID) Is Not Null) AND ((ConcatRelated("Name","ClassAssigned_Instructors","ClassID = " & [ClassID])) Is Not Null));

I added the "Is Not Null" trying to resolved the 3075 error, but, this did not help.

I am using four tables for this report:

• Class (which is the main table - Class Title, Start Date, etc)
• ClassLog (which is used for the Main Report- Information given, breaks, etc)
• ClassAssigned (this links Students and Instructors to Class)
• Instructors (holds instructor information Name, email, etc)

 

[CJ_London]

The syntax looks OK in principle but a couple of changes required in the detail.

The field 'name' is a reserved word so it is probably getting confused with the name property for the ClassAssigned_Instructors query. To solve, put name into square brackets and if this does not work , change it in your query - for example to 'CName'.

There also appears to be a space in " ClassID = " between the " and the C in the first and second iterations which presumably shouldn't be there? If it should, you need to use square brackets to include the space

 

[Ringlis1]

CJ_London, thanks for the reply. I changed from Name to iName. I checked the space between the " C, however, there is no space, so it must have been the way it was posted to the forum.
I still cannot get the sub-report to view the data or the field :banghead:

SELECT ClassAssigned.ClassID, ConcatRelated("InstructorName","ClassAssigned_Instructors","ClassID = " & [ClassID]) AS InstructorList
FROM ClassAssigned
GROUP BY ClassAssigned.ClassID, ConcatRelated("InstructorName","ClassAssigned_Instructors","ClassID = " & [ClassID])
HAVING (((ClassAssigned.ClassID) Is Not Null) AND ((ConcatRelated("InstructorName","ClassAssigned_Instructors","ClassID = " & [ClassID])) Is Not Null));

 

[CJ_London]

OK, I presume the error 3075 is now solved - so to check a few basics in the data.

What is the value for ClassID and are there any records in ClassAssigned_Instructors which have this ID - also presumably both are numeric?

Also, I think you can remove the having clause - it's not doing anything

I don't think it will make any difference but you could try changing

"ClassID = " & [ClassID]

to

"ClassID = " & ClassAssigned.ClassID

On other thought - how many instructors are you expecting to get returned? there may be too many characters so the field width is oversize

 

[Ringlis1]

The 3075 error still occurs in the report. I have 1 class with only 1 instructor assigned and another class with 4 instructors assigned. So I am not close to the 255 character limitation.


ClassAssigned Table

• AssignedClassID (Autonumber)
• ClassID (Number field to link to class table)
• StudentID (Number field to link to Students table)
• StaffID (Number field to link to Instructors table)
• Workshop (Y/N)
• Skills (Y/N)
• Simulation(Y/N)
• Completed (Y/N)

ClassAssigned_Instructors Query (Includes ClassAssigned and Instructors Table)

• ClassID.ClassAssigned
• StaffAssigned.ClassAssigned
• InstructorName: iName.Instructors
• Department.Instructors
• Workshop.ClassAssigned
• Skills.ClassAssigned
• Simulation.ClassAssigned

 

[CJ_London]

Might have another possibility.

Have just looked at the concatrelated function from Allen Browne and see that the default separator is a comma (which will conflict with the commas used in the query)

Public Function ConcatRelated(strField As String, _
    strTable As String, _
    Optional strWhere As String, _
    Optional strOrderBy As String, _
    [COLOR=red]Optional strSeparator = ", "[/COLOR]) As Variant

I think there are two possible solutions, one is to define a different separator i.e.

ConcatRelated("InstructorName","ClassAssigned_Instructors","ClassID = " & [ClassID],,";")

The other is to surround the ConcatRelated function with quotation marks.

 

[Ringlis1]

I tried the two options and still not working. I changed the comma in the Module Code and added the semicollen in the to the end of the statement.

What I dont understand is why does the subreport alone show the results correctly. However, when a make that report a subreport and join/link "ClassID" the results are blank in the subreport.

 

[CJ_London]

Feel I'm thashing around on this one!

Does it work if you just use this part of the code?

SELECT ClassAssigned.ClassID, ConcatRelated("InstructorName","ClassAssigned_Instructors","ClassID = " & [ClassID]) AS InstructorList
FROM ClassAssigned

If so, then the issue is with the GROUP BY

If this doesn't work, is there any chance you can upload a sample of the db with some test data and the ConcatRelated code

 

[Ringlis1]

The concat works in a query and on an individual report. The problem is trying to get it to work on the form I need it to work on.:banghead:

 

[Ringlis1]

I think I need 1 more post so that I can upload the file

 

[Ringlis1]

Here is the access file - It only has test data at this point.

 

[Ringlis1]

I removed the subreport "InstructorList subreport" from the main report I am trying to get this to work on "Report_Class_Log_Notes" in the zip file. So you will have to add it back to the report.

 

[CJ_London]

Solved it!

In your report you have ClassID mixed up with Class#

So the Class# control has a control source of Class# but a control name of ClassID

Whilst the control with ClassID as a source is called Text34

So you query was actually trying to apply the Class# value which is Null - hence the error

Changing your where part to

=ConcatRelated("InstructorName","ClassAssigned_Instructors","ClassID = " & [Text34])

Solves the problem

I've reattached the amended db

 

[Ringlis1]

Thank You!

It now works like a charm. I was thinking it has to be something simple, but, it was driving me nuts :banghead: trying to find it.

 

[CJ_London]

Glad to help - might be a idea to regularise your control names and source names going forward!