ConcatRelated in a Report/SubReport (Allen Browne Module)

Ringlis1

Registered User.
Local time
Today, 05:21
Joined
Mar 18, 2013
Messages
13
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)
 
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
 
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:

Code:
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));
 
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
 
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
 
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)

Code:
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.
 
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.
 
Feel I'm thashing around on this one!

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

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
 
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:
 
I think I need 1 more post so that I can upload the file
 
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.
 
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

Code:
=ConcatRelated("InstructorName","ClassAssigned_Instructors","ClassID = " & [Text34])
Solves the problem

I've reattached the amended db
 

Attachments

Thank You! :D:D:D

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.
 
Glad to help - might be a idea to regularise your control names and source names going forward!
 

Users who are viewing this thread

Back
Top Bottom