Error on SQL statement \ Query

[DC CS]

Good day all,
I am running a QueryDef to create a query for a report. When the code is executed, I receive the below error:
"The specified field '[FieldName]' could refer to more than one table listed in the FROM clause of your SQL statement."

I understand the error, but I cannot find the error in the SQL statement. Below the code:

            qDef.SQL = "SELECT DISTINCT Tbl_Certification.StudentID, Tbl_StudentReg.AltIDNumber, Tbl_StudentReg.UseAltIDOnDocs, " _
                    & "Tbl_StudentReg.FirstName, Tbl_StudentReg.Surname, Tbl_Certification.CourseNumber, " _
                    & "Tbl_Courses.CourseDescription, " _
                    & "Tbl_Certification.StartDate , Tbl_Certification.EndDate, Tbl_Certification.AssDate, Tbl_Certification.CertDate, " _
                    & "Tbl_Certification.Facilitator, Tbl_Facilitators.FacilitatorName, " _
                    & "Tbl_Certification.Assessor, Tbl_Assessors.AssName , Tbl_Certification.Moderator, Tbl_Assessors_1.AssName, " _
                    & "Tbl_Certification.Employer, Tbl_Employer.EmployerName, Tbl_Certification.CertNumber, " _
                    & "Tbl_Certification.Result_Theory, Tbl_Certification.Result_Practical , Tbl_Certification.Result_Assignment, " _
                    & "Tbl_Certification.Result_Final_C_NYC, Tbl_Certification.AltCourseDescriptor, " _
                    & "Tbl_Certification.AltCourseSequenceNo, Tbl_CertificateNumbers.CertificateNumberPrefix, " _
                    & "Tbl_CertificateNumbers.CertificateMonthYear " _
                    & "FROM Tbl_CertificateNumbers INNER JOIN ((((((Tbl_Certification INNER JOIN Tbl_StudentReg ON " _
                    & "Tbl_Certification.StudentID = Tbl_StudentReg.IDNumber) INNER JOIN Tbl_Courses ON " _
                    & "Tbl_Certification.CourseNumber = Tbl_Courses.CourseNumber) INNER JOIN Tbl_Facilitators ON " _
                    & "Tbl_Certification.Facilitator = Tbl_Facilitators.FacilitatorID) INNER JOIN Tbl_Assessors ON " _
                    & "Tbl_Certification.Assessor = Tbl_Assessors.IDNumber) INNER JOIN Tbl_Assessors AS Tbl_Assessors_1 ON " _
                    & "Tbl_Certification.Moderator = Tbl_Assessors_1.IDNumber) INNER JOIN Tbl_Employer ON " _
                    & "Tbl_Certification.Employer = Tbl_Employer.EmployerNo) ON " _
                    & "(Tbl_Certification.CertNumber = Tbl_CertificateNumbers.CertificateCertNumber) AND " _
                    & "(Tbl_CertificateNumbers.CertificateStudent = Tbl_StudentReg.IDNumber) " _
                    & "WHERE Tbl_Certification.CertNumber = " & Val([Forms]![Frm_Results].[Text0]) & ";"
                            
                    qDef.Close
                    db.Close
                    Set qDef = Nothing
                    Set db = Nothing
                    Application.RefreshDatabaseWindow

The field in question, is "AssName". As can be seen, I am preceding the field name with the table name, but I still receive the error.



I am opening a report from the main form. The above code is in the "Report_Open" event. While the code executes in the "Open" event, no error occurs. When the "Exit Sub" runs in the "Open" event, the code jumps back to the Main form and displays the error message. The report's "Load" event does not run.

I trust I have explained the issue in enough detail.

Your assistance is highly appreciated

Thanks

[ArviLaanemets]

You have the same field name twice in result table
"..., Tbl_Assessors.AssName, ..., Tbl_Assessors_1.AssName, ..."
Try instead
"..., Tbl_Assessors.AssName, ..., Tbl_Assessors_1.AssName As AssName1, ..."

[CJ_London]

A good way to find the problem is to assign the sql to a string, debug.print the string then assign to your qdef.

Then if you get a problem, copy the sql string from the immediate window and paste to a new query. You can then use the query window to help you see where the problem lies

[DC CS]

Thanks ArviLaanemets, problem solved.